This template enables users to calculate the annual investment return that results from the investment of a monthly annuity. Users are required to enter the monthly annuity amounts and the appropriate investment valuation in order to calculate an annual investment return. The template also facilitates including a lump sum in the investment return calculation, calculating an investment valuation forecast and calculating a cumulative annual investment return. The investment return calculations in this unique template can be applied to most annuity type investments and can even be used to calculate the investment return that results from pension fund contributions.

The following sheets are included in the template:
ROI - enter the monthly annuity amounts and the investment valuation on this sheet in order to automatically calculate the annual investment return. The monthly and annual periods are determined by the start date that is entered in cell D2 and a lump sum amount at the beginning of the investment period can be entered in cell G2. The forecast section can be used to calculate an investment valuation forecast.
Balances - this sheet includes a detailed calculation of the investment valuation and annual investment return (column A to G) based on the values that are entered on the ROI sheet. The calculations in columns I to L can be used in order to calculate the cumulative annual investment return. The Goal Seek feature should be used in order to produce an accurate calculation of the overall investment return.
Chart - the chart on this sheet displays the annual and cumulative annual investment return.

Annual Investment Return Calculation

User input

All the investment return calculations in this template are based on the monthly annuity amounts that are entered in the cell range from cell B6 to U17 on the ROI sheet. Users are also required to enter a valuation for the annuity investment in row 20. All the cells with a yellow cell background require user input and the cells with a light blue cell background contain formulas.

The start date that is specified in cell D2 determines the monthly periods that are included in column A and the annual periods that are included in row 5. All investment return calculations are based on the assumption that the appropriate annuity amounts are paid at the end of each monthly period - the start date that is specified in cell D2 should therefore not be the date of the first payment, but a date one month before the first annuity amount is paid.

Note: The start date should be entered when you start using the template and should not be amended subsequently. If you amend the start date, the annuity amounts that have been entered in the cell range from cell B6 to U17 may not match the correct monthly periods.

The lump sum amount that is specified in cell G2 is included at the beginning of the investment period for annual investment return calculation purposes. A value should be entered in this cell if a lump sum amount is invested in addition to the monthly annuity amounts that are specified in cells B6 to U17. If there is no lump sum investment at the beginning of the investment period, a nil value should be entered in cell G2.

All monthly annuity amounts should be entered in the cell range from cell B6 to U17 and should match the appropriate monthly periods that are included in column A and the annual periods that are included in row 5. It is also important to note that the annuity amounts should match the valuation amounts that are entered in row 20.

For example: If annuity amounts have been entered up to the December 2010 monthly period, the valuation that is entered in row 20 should be the investment valuation at the end of December 2010. If the annuity amounts do not match the valuation date, the annual investment return calculations will not be accurate.

Note: You don't have to enter annuity amounts for a full 12 month period. As long as the valuation date matches the annuity amounts that are entered, the annual investment return will be calculated accurately. You can therefore calculate the appropriate annual investment return on an ongoing basis - if you've only entered 3 annuity amounts and you've entered the investment valuation at the end of the 3rd month, the annual investment return calculation is calculated for the period that ends after the 3rd month.

The investment valuations that are entered in row 20 can be obtained from the monthly statements that are received from the financial institution with which the annuity is invested. In order to calculate and monitor the investment return that results from an annuity investment, it is therefore imperative that regular monthly statements are obtained from the appropriate financial institution and that these statements reflect a valuation of the investment.

Investment Return Calculations

The ROI sheet includes calculations of the cumulative contributions, investment surplus or deficit, investment surplus or deficit percentage, the annual investment return percentage and the cumulative annual investment return percentage. All of these calculations are automated except for the cumulative annual investment percentage - refer to the Cumulative Annual Investment Return Calculation section of these instructions for more information on how the cumulative investment return should be calculated.

The cumulative contributions amount that is calculated in row 22 is the sum of all the appropriate monthly annuity amounts and the lump sum that is entered in cell G2. This amount therefore reflects the total funds that have been paid into the annuity investment.

The surplus or deficit amounts in row 23 are calculated by deducting the appropriate cumulative contribution amounts from the valuation amounts that are entered in row 20. The surplus or deficit percentages in row 24 are calculated by dividing the surplus or deficit amounts by the cumulative contribution amounts. These values therefore indicate whether the investment valuation exceeds the funds that have been contributed to the investment - if the values are negative, it indicates that the investment is worth less than the funds that have been invested.

The annual return percentage that is calculated in row 25 is based on the annuity amounts that are entered in row 6 to 17 and the growth of the investment which is determined by including the previous year's valuation as the present value (PV) and the current year's valuation as the future value (FV) in the calculation. The average monthly annuity amount is calculated and included in the calculation as the monthly payment amount.

Note: The monthly payments that are associated with most annuity type investments are usually subject to a single annual escalation percentage which means that the annuity payments remain constant for each 12 month period that forms part of the investment term. Our calculation of the annual investment return is therefore based on the assumption that monthly payments remain constant over each 12 month period.

Note: If the monthly annuity amounts are not constant, the annual investment return that is calculated will not be 100% accurate but because we calculate the return based on a 12 month average monthly payment, the actual investment return will not differ significantly from our calculation. The template can therefore be used for both constant and variable monthly annuities.

Investment Forecast

An annuity investment forecast has been included in rows 29 to 33 on the ROI sheet. Users are required to enter an initial monthly annuity amount, an annual escalation percentage and a forecasted annual investment return in order to compile an investment valuation forecast.

The annual annuity escalation percentage that is entered in row 30 is applied to the initial annuity amount that is entered in cell B29 in order to calculate the monthly annuity amounts in row 32. These amounts are included in the investment valuation forecast calculation as the monthly payment amounts; the forecasted annual investment return (row 31) is included as the rate of return and the previous period's valuation is included as the present value (PV).

We recommend entering initial investment return expectations into the forecast section on the ROI sheet and then using the annual investment return calculations in order to measure actual annual investment returns against the forecasted investment returns. The forecast calculations can also be amended throughout the investment term in order to facilitate a comparison between a revised forecast and actual annual investment returns.

Annuity Investment Balances

The calculations in column A to G on the Balances sheet have been included in the template in order to provide a detailed analysis of the monthly annuity investment balances. All these calculations are based on the values that are entered on the ROI sheet. If the monthly annuity amounts for each 12 month period are constant, the balances that are calculated in column F should agree to the valuation amounts that are entered in row 20 on the ROI sheet.

If the monthly annuity amounts for each 12 month period are not constant, you may notice that the investment balances that are calculated in column F differ from the appropriate valuation amounts but these differences will not be significant and will not have a significant influence on the annual investment return that is calculated.

The calculations in column I to L on the Balances sheet have been included in the template to enable users to calculate a cumulative annual investment return that is 100% accurate.

Cumulative Annual Investment Return Calculation

Calculation Methodology

The calculation of a cumulative annual investment return for annuity investments is quite complex because the monthly annuity amounts are not usually constant over an investment period of longer than 12 months and the monthly annuity amounts have to be allocated to the correct monthly investment period in order to calculate a 100% accurate overall annual investment return.

As we've mentioned before, the annual investment return is calculated based on the assumption that the monthly annuity amounts that are included in the appropriate 12 month period remain constant. This is an assumption that holds true for most annuity investments and even if the monthly annuity amounts are not constant, the investment return that is calculated over the appropriate 12 month period based on the average monthly annuity amount should not differ significantly from the actual investment return.

The assumption of a constant monthly annuity amount should however not be applied to an investment term of longer than 12 months. The monthly annuity amounts of a typical annuity investment will differ over periods of longer than 12 months and if an average monthly annuity amount is used to calculate an overall annual investment return, the calculation result is guaranteed to be inaccurate.

Note: The annual annuity investment return in this template (row 25 on the ROI sheet) is calculated by using the RATE function and basing the calculation on the average monthly annuity amount for the appropriate 12 month period. As we've just explained, this function can therefore not be used in order to calculate a cumulative annual investment return because the calculation result will not be accurate.

Another function that can be considered when calculating the cumulative annual investment return is the IRR function. This function incorporates variable cash flows but can only be calculated on an annual basis. The problem with applying this calculation methodology to monthly annuity investments is that the total annual cash flows are used in the calculation and the actual annuities are paid on a monthly basis. The total annual cash outflow that results from the payment of annuities can therefore only be included at the start or end of the appropriate 12 month period for the purpose of the IRR calculation.

When using the IRR function, the cash flows of the annuity investment are therefore not matched to the appropriate monthly periods and the calculation result is therefore guaranteed to be inaccurate. This is a shortcoming of all IRR based calculations and an important attribute that users should take into account whenever they encounter an annuity investment return calculation that is based on the IRR function.

The conclusion that can be reached based on the above is that there is no single Excel function that can calculate an accurate cumulative investment return for an annuity investment and it is therefore impossible to include this calculation in a single cell.

In order to calculate an accurate cumulative annual investment return, some level of manual calculation is therefore required. We have facilitated this calculation in columns I to L on the Balances sheet and we'll now explain how you can use the Goal Seek Excel feature in order to calculate an accurate cumulative annual investment return for an annuity investment over any investment term.

Manual Calculation

Our manual calculation of the cumulative annual investment return in columns I to L on the Balances sheet includes an opening balance, an investment return percentage, an investment growth amount and a closing balance. The monthly annuity amounts that are entered on the ROI sheet and included in column D on the Balances sheet are also taken into account in our cumulative annual investment return calculation.

This calculation may seem complicated but it has been set up so that it is actually quite simple. Users are only required to specify the appropriate investment valuation amount (closing balance) in column L and the cumulative investment return percentage is then calculated automatically.

An investment valuation at the end of the investment period for which the cumulative annual investment return is calculated should be specified in this column. The list box in cell J3 can be used to highlight the appropriate monthly closing balance - after selecting the appropriate month from this list box, the cell that contains the appropriate closing balance (investment valuation amount) in column L will be highlighted. The value in this cell needs to be replaced by the appropriate valuation amount in order to calculate the cumulative annual investment return.

It is however not simply a question of replacing the calculated closing balance with the appropriate valuation amount - we actually need to determine which annual investment return percentage would result in the closing balance in column L equaling the appropriate investment valuation amount. This can be achieved on a trial and error basis by entering a number of different investment return percentages in the cell L3 (which determines the investment return percentage values that are included in column J) until the closing balance in column L equals the appropriate investment valuation amount or we can use the Goal Seek Excel feature to determine this value instantly.

Step by Step Instructions - Goal Seek

The following steps need to be completed in order to calculate the cumulative annual investment return.

Step 1 - We assume that you've entered the appropriate monthly annuity payments on the ROI sheet and that you entered the appropriate investment valuation in row 20 on the ROI sheet. You now need to find the appropriate closing balance in column L on the Balances sheet and replace this value with the appropriate valuation amount. This can be accomplished by simply selecting the appropriate month from the list box in cell J3 on the Balances sheet. After selecting the appropriate month from this list box, the appropriate closing balance will be highlighted in orange. You then need to select the cell that has been highlighted.

Step 2 - Access the Goal Seek Excel feature by selecting the What-If Analysis feature from the Data Tools section on the Data tab of the ribbon. The Goal Seek feature can be selected from the menu that is displayed after accessing the What-If Analysis feature.

Step 3 - The Goal Seek dialog box includes 3 input fields. The first is the "Set Cell" input field - if you've selected the cell that is highlighted in orange before you accessed the Goal Seek feature, the appropriate cell reference will already be included in the input field. If the correct cell is not included in the input field, simply select the correct cell by clicking the icon next to the input field.

Step 4 - Enter the appropriate investment valuation amount in the "To value" input field. This value should be the same as the valuation amount that has been entered on the ROI sheet.

Note: If the annuity amounts that have been entered on the ROI sheet are constant for each 12 month period, the closing balance in column F on the Balances sheet should agree to the valuation amount that you need to enter in this input field. If the monthly annuity amounts are not constant, the closing balance in column F may differ from the valuation amount but the difference will not be significant.

Step 5 - Select the "By changing cell" input field and select cell L3. As we've mentioned before, this cell determines the annual investment return percentages that are included in column J on the Balances sheet and should therefore always be selected in the third input field of the Goal Seek function.

Step 6 - Click the OK button. A message should then be displayed to indicate that the appropriate value has been calculated and the value in cell L3 should automatically change to the appropriate cumulative annual investment return percentage. This value will also be displayed in column J on the Balances sheet.

Step 7 - Copy the value in cell L3 and paste it as a value into the appropriate cell in row 26 on the ROI sheet. This will ensure that you keep record of all the appropriate cumulative annual investment return percentages that have been calculated and that the chart on the Chart sheet is updated accordingly.

Note: The closing balances in columns F and L on the Balances sheet are not supposed to be the same because the amounts in these columns are not calculated on the same basis. The only values that should agree are the closing balances of the period that has been included in the cumulative investment return calculation. The balances for this period will also only be exactly the same if the monthly annuity amounts that are included in each annual period are constant.

Annual Investment Return Chart

The chart on the Chart sheet displays the investment return for each annual period (row 25 on the ROI sheet) and the cumulative investment return that has been calculated manually and copied into row 26 for each annual period. The chart therefore facilitates viewing the investment returns that are achieved during each annual investment period and the effect that the annual investment returns have on the cumulative annual investment return.

Note: If the chart contains an annual investment return percentage for a particular month but no cumulative annual investment return percentage, it means that the appropriate cumulative annual investment return percentage has not been copied into row 26 on the ROI sheet.