Note: I found an error in the spreadsheet calculations, so if you saved a copy before 3/10/2021, download it again.
These calculations could come in handy in various scenarios. Say the firm you work for has a pension plan that they want to close out and pay off participants early. What would those payouts in the future be worth now? Or you own an annuity that will pay you a certain amount each quarter for life, starting in five years, but you have the option to sell the annuity now. In both cases, what you need is to calculate the present value of getting the proceeds of future payments, in the form of a lump sum payment.
The above is an example of a 60-year-old that expects to receive $3,500 a month from a pension for life starting in 5 years, when he is sixty-five. To calculate the current value, we need to make some assumptions. First, assume the life expectancy for this person is seventy-seven. So, we use 144 months which is 12 years from retirement. Now we need an interest rate to discount those future payment for the 12 years. In this case we assume 5.00%, compounded monthly. The payment type is not that important at this point, so we assume “End of Period”. That calculation yields a present value 5 years from now of $378,423.
But how much is it worth today? We need a second present value calculation on the $378,423, using 5.00%, compounded annually and get a current value of $296,504.
This simple calculation does not take into consideration taxes for taking a lump sum, nor what tax rates will be like in the future.