Varying the annuity period

There is an element in the equation used to give the value of the annuity that is variable - the period over which it is paid. An annuity is normally paid for the rest of your life so what happens as you vary the value for years in B3? To investigate this we can use Scenario Manager which is accessed via the Tools menu. As this is an infrequently used menu item you will probably need to click the may need to click the Expand button at the bottom of the Tools menu to see the full list and select the Scenarios item.

When the Scenario Manager dialog opens click the Add button.

We want to devise four possible scenarios. Here we show the first one being set up. After clicking Add in the Scenario Manager type the Scenario name "Shorter (5 yrs)" and enter B3 in the Changing cells box and click OK.

In the next dialog enter 5 as the value and click Add.

Repeat with three more scenarios "Slightly Longer (12 yrs)", "Longer (15yrs)" and "Very Long (20yrs).

Then click on the Summary button, In the Scenario Summary box that appears select Scenario Summary as the Report type and ensure that B7 is entered in the Result cells box and click OK.

The Scanrio Summary is created in its own page in the spreadsheet and shows that your gamble pays off if you are paid the annuity for 12 years and you do very well indeed from it if it is paid for 20 years. It was a bad buy however in the event of the Shorter 5yrs scenario. To see this click the screen dump to enlarge it.

<ASIN:1932925015>