As discussed in my previous post, the basic principle underlying the Actuarial Approach is that to be considered in actuarial balance, a retiree's assets (current assets plus the present value of future expected benefit payments or payments from other sources of income) must be equal to the retiree's liabilities (the present value of future expected expenses/amounts left to heirs). Our last post illustrated this concept with Richard’s Actuarial Balance Sheet. Several of my readers have asked me, however, how the present values shown in Richard’s actuarial balance sheet were calculated. While all the values can be obtained by using the “Excluding Social Security 3.1” spreadsheet, use of this spreadsheet to calculate such values may not be all that intuitive. Therefore, I have added a new Excel spreadsheet to help readers called “Simple Present Value Calculator.” It is located in the “Articles and Spreadsheet” section of this website.
The Simple Present Value Calculator can be used to calculate 1) the present value of a single payment (or estimated expense) or 2) a stream of annual payments/expenses. If the spreadsheet is used to calculate the present value of a stream of payments, the stream must either be the same amount per year or increase at a specific rate per year. The spreadsheet assumes beginning of year payments.
For example, Richard’s Social Security benefit was assumed to be $20,340 per annum. We assumed that this benefit would commence immediately (T=0), increase by 2.5% per year and would be payable for 27 years, based on an assumption of death at age 95. We also assumed a 4.5% discount rate. Inputting these items in the spreadsheet, we develop the present value of his Social Security benefit under these assumptions equal to $432,037 (one of Richard’s assets).
If you want to play with this new spreadsheet, see if you can duplicate the present value of Richard’s life annuity benefit of $15,000 per year (with no future increases) payable for 27 years or the present value of Richard’s essential non-health related expenses of $50,000 per year, increasing by 2.5% each year and assumed in this example to be payable for 25 years.
You can also use this simple spreadsheet for other (perhaps even more important) purposes. For example, if you win the Lottery, you can use it to determine the effective interest rate used to discount future lottery payments for the lump sum option. Since the Powerball Lottery was big news this week, this was an issue discussed in the New York Times article, Dear Powerball Winner Take Our Advice and Take the Annuity. If we take the author at his word and the choice is either $50 million (pre-tax) annual payment over 30 years (a total of $1.5 billion) or $930 million in a lump sum (also pre-tax), then the discount rate for this transaction is approximately 3.7% per annum. The author must have done his calculations on a post-tax basis (or simply made an error) to come up with an effective rate of 2.843%.
