Excel Skills UK | Annuity Investment Return Template
Instructions
www.excel-skills.co.uk
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.
Page 1 of 19
Excel Skills UK | Annuity Investment Return Template
Instructions
www.excel-skills.co.uk
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.
Page 2 of 19
Excel Skills UK | Annuity Investment Return Template
Instructions
www.excel-skills.co.uk
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.
Page 3 of 19
Excel Skills UK | Annuity Investment Return Template
Instructions
www.excel-skills.co.uk
Page 4 of 19
Excel Skills UK | Annuity Investment Return Template
Instructions
www.excel-skills.co.uk
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.
Page 5 of 19
Excel Skills UK | Annuity Investment Return Template
Instructions
www.excel-skills.co.uk
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.
Page 6 of 19
Excel Skills UK | Annuity Investment Return Template
Instructions
www.excel-skills.co.uk
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.
Page 7 of 19
Excel Skills UK | Annuity Investment Return Template
Instructions
www.excel-skills.co.uk
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.
Page 8 of 19
Excel Skills UK | Annuity Investment Return Template
Instructions
www.excel-skills.co.uk
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.
Help & Customization
If you experience any difficulty while using this template and you are not able to find the appropriate guidance in these
instructions, please e-mail us at support@excel-skills.co.uk for assistance. This template has been designed with flexibility
in mind to ensure that it can be used in most business environments. If however you need an Excel based template that is
customized specifically for your business requirements, please e-mail our Support function and provide a brief explanation
of your requirements.
Page 9 of 19
Monthly Annuities www.excel-skills.co.uk
Return on Investment Calculation Start Date 8/1/2011 Lump sum -
1 2 3 4 5 6 7 8 9 10
Month 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020
August 500.00 525.00 551.25 584.30 631.20 669.10 709.20 751.80 796.90 844.70
September 500.00 525.00 551.25 584.30 631.20 669.10 709.20 751.80 796.90 844.70
October 500.00 525.00 551.25 584.30 631.20 669.10 709.20 751.80 796.90 844.70
November 500.00 525.00 551.25 584.30 631.20 669.10 709.20 751.80 796.90 844.70
December 500.00 525.00 551.25 584.30 631.20 669.10 709.20 751.80 796.90 844.70
January 500.00 525.00 551.25 584.30 631.20 669.10 709.20 751.80 796.90 844.70
February 500.00 525.00 551.25 584.30 631.20 669.10 709.20 751.80 796.90 844.70
March 500.00 525.00 551.25 584.30 631.20 669.10 709.20 751.80 796.90 844.70
April 500.00 525.00 551.25 584.30 631.20 669.10 709.20 751.80 796.90 844.70
May 500.00 525.00 551.25 584.30 631.20 669.10 709.20 751.80 796.90 844.70
June 500.00 525.00 551.25 584.30 631.20 669.10 709.20 751.80 796.90 844.70
July 500.00 525.00 551.25 584.30 631.20 669.10 709.20 751.80 796.90 844.70
Total 6,000.00 6,300.00 6,615.00 7,011.60 7,574.40 8,029.20 8,510.40 9,021.60 9,562.80 10,136.40
Valuation 6,200.00 13,300.00 21,450.00 30,200.00 38,950.00 50,700.00 62,020.00 75,400.00 88,200.00 100,600.00
Cumulative Contributions 6,000.00 12,300.00 18,915.00 25,926.60 33,501.00 41,530.20 50,040.60 59,062.20 68,625.00 78,761.40
Surplus / (Deficit) 200.00 1,000.00 2,535.00 4,273.40 5,449.00 9,169.80 11,979.40 16,337.80 19,575.00 21,838.60
Surplus / (Deficit) % 3.3% 8.1% 13.4% 16.5% 16.3% 22.1% 23.9% 27.7% 28.5% 27.7%
Annual Return 7.1% 8.5% 9.0% 6.8% 3.4% 8.4% 5.0% 6.4% 4.0% 2.4%
Cumulative Annual Return 7.1% 8.2% 8.7% 7.8% 6.1% 6.9% 6.3% 6.4% 5.8% 5.1%
Forecast
Initial monthly annuity 500.00
Annual annuity escalation 6.0% 6.0% 6.0% 6.0% 6.0% 6.0% 6.0% 6.0% 6.0%
Forecasted Annual Return 8.0% 8.0% 8.0% 8.0% 8.0% 8.0% 8.0% 8.0% 8.0% 8.0%
Monthly Annuity 500.00 530.00 561.80 595.48 631.21 669.08 709.23 751.78 796.89 844.70
Valuation 6,224.96 13,340.09 21,441.68 30,635.01 41,036.20 52,772.20 65,982.08 80,818.16 97,447.21 116,051.72
Page 10 of 15
Monthly Annuities
www.excel-skills.co.uk www.excel-skills.co.uk
Return on Investment Calculation
11 12 13 14 15 16 17 18 19 20
Month 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030
August 895.40 949.10 1,006.05 1,066.40 1,130.40 1,198.20 1,270.15 1,346.30 1,427.10 1,512.70
September 895.40 949.10 1,006.05 1,066.40 1,130.40 1,198.20 1,270.15 1,346.30 1,427.10 1,512.70
October 895.40 949.10 1,006.05 1,066.40 1,130.40 1,198.20 1,270.15 1,346.30 1,427.10 1,512.70
November 895.40 949.10 1,006.05 1,066.40 1,130.40 1,198.20 1,270.15 1,346.30 1,427.10 1,512.70
December 895.40 949.10 1,006.05 1,066.40 1,130.40 1,198.20 1,270.15 1,346.30 1,427.10 1,512.70
January 895.40 949.10 1,006.05 1,066.40 1,130.40 1,198.20 1,270.15 1,346.30 1,427.10 1,512.70
February 895.40 949.10 1,006.05 1,066.40 1,130.40 1,198.20 1,270.15 1,346.30 1,427.10 1,512.70
March 895.40 949.10 1,006.05 1,066.40 1,130.40 1,198.20 1,270.15 1,346.30 1,427.10 1,512.70
April 895.40 949.10 1,006.05 1,066.40 1,130.40 1,198.20 1,270.15 1,346.30 1,427.10 1,512.70
May 895.40 949.10 1,006.05 1,066.40 1,130.40 1,198.20 1,270.15 1,346.30 1,427.10 1,512.70
June 895.40 949.10 1,006.05 1,066.40 1,130.40 1,198.20 1,270.15 1,346.30 1,427.10 1,512.70
July 895.40 949.10 1,006.05 1,066.40 1,130.40 1,198.20 1,270.15 1,346.30 1,427.10 1,512.70
Total 10,744.80 11,389.20 12,072.60 12,796.80 13,564.80 14,378.40 15,241.80 16,155.60 17,125.20 18,152.40
Valuation 118,560.00 141,240.00 157,780.00 180,920.00 210,430.00 241,800.00 280,150.00 312,080.00 340,200.00 390,820.00
Cumulative Contributions 89,506.20 100,895.40 112,968.00 125,764.80 139,329.60 153,708.00 168,949.80 185,105.40 202,230.60 220,383.00
Surplus / (Deficit) 29,053.80 40,344.60 44,812.00 55,155.20 71,100.40 88,092.00 111,200.20 126,974.60 137,969.40 170,437.00
Surplus / (Deficit) % 32.5% 40.0% 39.7% 43.9% 51.0% 57.3% 65.8% 68.6% 68.2% 77.3%
Annual Return 6.6% 8.8% 3.0% 6.1% 8.2% 7.6% 8.9% 5.4% 3.4% 8.9%
Cumulative Annual Return 5.4% 6.0% 5.5% 5.6% 6.0% 6.3% 6.7% 6.5% 6.0% 6.4%
Forecast
Initial monthly annuity
Annual annuity escalation 6.0% 6.0% 6.0% 6.0% 6.0% 6.0% 6.0% 6.0% 6.0% 6.0%
Forecasted Annual Return 8.0% 8.0% 8.0% 8.0% 8.0% 8.0% 8.0% 8.0% 8.0% 8.0%
Monthly Annuity 895.38 949.10 1,006.05 1,066.41 1,130.40 1,198.22 1,270.12 1,346.32 1,427.10 1,512.73
Valuation 136,831.40 160,004.61 185,810.18 214,509.10 246,386.63 281,754.38 320,952.71 364,353.24 412,361.70 465,420.88
Page 11 of 15
Monthly Annuities Cumulative Investment Return Calculation
Monthly Annuity Balance & Annual Return on Investment Calculations
Select Month Jul-2031 6.4%
Payment Opening Annuity Investment Closing Investment Opening Investment Investment Closing
Month Number Balance Amount Growth Balance Return % Balance Return % Growth Balance
Aug-2011 1 - 500.00 - 500.00 7.1% - 6.4% - 500.00
Sep-2011 2 500.00 500.00 2.97 1,002.97 7.1% 500.00 6.4% 2.68 1,002.68
Oct-2011 3 1,002.97 500.00 5.96 1,508.93 7.1% 1,002.68 6.4% 5.38 1,508.06
Nov-2011 4 1,508.93 500.00 8.97 2,017.89 7.1% 1,508.06 6.4% 8.10 2,016.16
Dec-2011 5 2,017.89 500.00 11.99 2,529.88 7.1% 2,016.16 6.4% 10.83 2,526.99
Jan-2012 6 2,529.88 500.00 15.03 3,044.91 7.1% 2,526.99 6.4% 13.57 3,040.56
Feb-2012 7 3,044.91 500.00 18.09 3,563.01 7.1% 3,040.56 6.4% 16.34 3,556.90
Mar-2012 8 3,563.01 500.00 21.17 4,084.17 7.1% 3,556.90 6.4% 19.12 4,076.02
Apr-2012 9 4,084.17 500.00 24.27 4,608.44 7.1% 4,076.02 6.4% 21.91 4,597.93
May-2012 10 4,608.44 500.00 27.38 5,135.82 7.1% 4,597.93 6.4% 24.73 5,122.66
Jun-2012 11 5,135.82 500.00 30.51 5,666.33 7.1% 5,122.66 6.4% 27.56 5,650.21
Jul-2012 12 5,666.33 500.00 33.67 6,200.00 7.1% 5,650.21 6.4% 30.40 6,180.61
Aug-2012 13 6,200.00 525.00 43.95 6,768.95 8.5% 6,180.61 6.4% 33.27 6,738.88
Sep-2012 14 6,768.95 525.00 47.98 7,341.94 8.5% 6,738.88 6.4% 36.32 7,300.20
Oct-2012 15 7,341.94 525.00 52.05 7,918.98 8.5% 7,300.20 6.4% 39.39 7,864.59
Nov-2012 16 7,918.98 525.00 56.14 8,500.12 8.5% 7,864.59 6.4% 42.49 8,432.08
Dec-2012 17 8,500.12 525.00 60.26 9,085.38 8.5% 8,432.08 6.4% 45.61 9,002.69
Jan-2013 18 9,085.38 525.00 64.41 9,674.78 8.5% 9,002.69 6.4% 48.75 9,576.44
Feb-2013 19 9,674.78 525.00 68.58 10,268.36 8.5% 9,576.44 6.4% 51.91 10,153.35
Mar-2013 20 10,268.36 525.00 72.79 10,866.16 8.5% 10,153.35 6.4% 55.09 10,733.44
Apr-2013 21 10,866.16 525.00 77.03 11,468.18 8.5% 10,733.44 6.4% 58.30 11,316.74
May-2013 22 11,468.18 525.00 81.30 12,074.48 8.5% 11,316.74 6.4% 61.53 11,903.27
Jun-2013 23 12,074.48 525.00 85.59 12,685.08 8.5% 11,903.27 6.4% 64.79 12,493.06
Jul-2013 24 12,685.08 525.00 89.92 13,300.00 8.5% 12,493.06 6.4% 68.06 13,086.12
Aug-2013 25 13,300.00 551.25 100.23 13,951.48 9.0% 13,086.12 6.4% 71.36 13,708.73
Sep-2013 26 13,951.48 551.25 105.13 14,607.86 9.0% 13,708.73 6.4% 74.86 14,334.84
Oct-2013 27 14,607.86 551.25 110.08 15,269.19 9.0% 14,334.84 6.4% 78.38 14,964.47
Nov-2013 28 15,269.19 551.25 115.06 15,935.51 9.0% 14,964.47 6.4% 81.93 15,597.64
Dec-2013 29 15,935.51 551.25 120.09 16,606.84 9.0% 15,597.64 6.4% 85.50 16,234.39
Jan-2014 30 16,606.84 551.25 125.14 17,283.24 9.0% 16,234.39 6.4% 89.10 16,874.75
Feb-2014 31 17,283.24 551.25 130.24 17,964.73 9.0% 16,874.75 6.4% 92.73 17,518.73
Mar-2014 32 17,964.73 551.25 135.38 18,651.36 9.0% 17,518.73 6.4% 96.39 18,166.37
Apr-2014 33 18,651.36 551.25 140.55 19,343.16 9.0% 18,166.37 6.4% 100.07 18,817.69
May-2014 34 19,343.16 551.25 145.77 20,040.17 9.0% 18,817.69 6.4% 103.79 19,472.73
Jun-2014 35 20,040.17 551.25 151.02 20,742.44 9.0% 19,472.73 6.4% 107.52 20,131.50
Jul-2014 36 20,742.44 551.25 156.31 21,450.00 9.0% 20,131.50 6.4% 111.29 20,794.05
Aug-2014 37 21,450.00 584.30 122.29 22,156.59 6.8% 20,794.05 6.4% 115.09 21,493.43
Sep-2014 38 22,156.59 584.30 126.31 22,867.20 6.8% 21,493.43 6.4% 118.88 22,196.62
Oct-2014 39 22,867.20 584.30 130.36 23,581.86 6.8% 22,196.62 6.4% 122.69 22,903.61
Nov-2014 40 23,581.86 584.30 134.44 24,300.60 6.8% 22,903.61 6.4% 126.53 23,614.44
Dec-2014 41 24,300.60 584.30 138.54 25,023.44 6.8% 23,614.44 6.4% 130.38 24,329.12
Jan-2015 42 25,023.44 584.30 142.66 25,750.39 6.8% 24,329.12 6.4% 134.26 25,047.68
Feb-2015 43 25,750.39 584.30 146.80 26,481.50 6.8% 25,047.68 6.4% 138.16 25,770.15
Mar-2015 44 26,481.50 584.30 150.97 27,216.77 6.8% 25,770.15 6.4% 142.09 26,496.53
Page 12 of 14
Monthly Annuities Cumulative Investment Return Calculation
Monthly Annuity Balance & Annual Return on Investment Calculations
Select Month Jul-2031 6.4%
Payment Opening Annuity Investment Closing Investment Opening Investment Investment Closing
Month Number Balance Amount Growth Balance Return % Balance Return % Growth Balance
Apr-2015 45 27,216.77 584.30 155.16 27,956.23 6.8% 26,496.53 6.4% 146.03 27,226.86
May-2015 46 27,956.23 584.30 159.38 28,699.90 6.8% 27,226.86 6.4% 150.00 27,961.16
Jun-2015 47 28,699.90 584.30 163.62 29,447.82 6.8% 27,961.16 6.4% 153.99 28,699.45
Jul-2015 48 29,447.82 584.30 167.88 30,200.00 6.8% 28,699.45 6.4% 158.00 29,441.75
Aug-2015 49 30,200.00 631.20 86.55 30,917.75 3.4% 29,441.75 6.4% 162.04 30,234.99
Sep-2015 50 30,917.75 631.20 88.60 31,637.55 3.4% 30,234.99 6.4% 165.89 31,032.08
Oct-2015 51 31,637.55 631.20 90.66 32,359.41 3.4% 31,032.08 6.4% 169.75 31,833.03
Nov-2015 52 32,359.41 631.20 92.73 33,083.35 3.4% 31,833.03 6.4% 173.62 32,637.85
Dec-2015 53 33,083.35 631.20 94.81 33,809.35 3.4% 32,637.85 6.4% 177.51 33,446.56
Jan-2016 54 33,809.35 631.20 96.89 34,537.44 3.4% 33,446.56 6.4% 181.40 34,259.16
Feb-2016 55 34,537.44 631.20 98.98 35,267.62 3.4% 34,259.16 6.4% 185.31 35,075.67
Mar-2016 56 35,267.62 631.20 101.07 35,999.88 3.4% 35,075.67 6.4% 189.23 35,896.10
Apr-2016 57 35,999.88 631.20 103.17 36,734.25 3.4% 35,896.10 6.4% 193.16 36,720.45
May-2016 58 36,734.25 631.20 105.27 37,470.72 3.4% 36,720.45 6.4% 197.10 37,548.75
Jun-2016 59 37,470.72 631.20 107.38 38,209.30 3.4% 37,548.75 6.4% 201.05 38,381.00
Jul-2016 60 38,209.30 631.20 109.50 38,950.00 3.4% 38,381.00 6.4% 205.01 39,217.21
Aug-2016 61 38,950.00 669.10 272.90 39,892.00 8.4% 39,217.21 6.4% 208.99 40,095.29
Sep-2016 62 39,892.00 669.10 279.50 40,840.61 8.4% 40,095.29 6.4% 214.04 40,978.43
Oct-2016 63 40,840.61 669.10 286.15 41,795.86 8.4% 40,978.43 6.4% 219.13 41,866.66
Nov-2016 64 41,795.86 669.10 292.84 42,757.80 8.4% 41,866.66 6.4% 224.25 42,760.02
Dec-2016 65 42,757.80 669.10 299.58 43,726.49 8.4% 42,760.02 6.4% 229.42 43,658.53
Jan-2017 66 43,726.49 669.10 306.37 44,701.96 8.4% 43,658.53 6.4% 234.61 44,562.25
Feb-2017 67 44,701.96 669.10 313.21 45,684.26 8.4% 44,562.25 6.4% 239.85 45,471.19
Mar-2017 68 45,684.26 669.10 320.09 46,673.45 8.4% 45,471.19 6.4% 245.12 46,385.41
Apr-2017 69 46,673.45 669.10 327.02 47,669.57 8.4% 46,385.41 6.4% 250.43 47,304.94
May-2017 70 47,669.57 669.10 334.00 48,672.67 8.4% 47,304.94 6.4% 255.77 48,229.81
Jun-2017 71 48,672.67 669.10 341.03 49,682.80 8.4% 48,229.81 6.4% 261.15 49,160.06
Jul-2017 72 49,682.80 669.10 348.10 50,700.00 8.4% 49,160.06 6.4% 266.57 50,095.73
Aug-2017 73 50,700.00 709.20 212.58 51,621.78 5.0% 50,095.73 6.4% 272.03 51,076.96
Sep-2017 74 51,621.78 709.20 216.44 52,547.42 5.0% 51,076.96 6.4% 276.98 52,063.13
Oct-2017 75 52,547.42 709.20 220.32 53,476.94 5.0% 52,063.13 6.4% 281.94 53,054.27
Nov-2017 76 53,476.94 709.20 224.22 54,410.36 5.0% 53,054.27 6.4% 286.93 54,050.40
Dec-2017 77 54,410.36 709.20 228.13 55,347.69 5.0% 54,050.40 6.4% 291.94 55,051.54
Jan-2018 78 55,347.69 709.20 232.06 56,288.96 5.0% 55,051.54 6.4% 296.97 56,057.71
Feb-2018 79 56,288.96 709.20 236.01 57,234.17 5.0% 56,057.71 6.4% 302.02 57,068.92
Mar-2018 80 57,234.17 709.20 239.97 58,183.34 5.0% 57,068.92 6.4% 307.09 58,085.21
Apr-2018 81 58,183.34 709.20 243.95 59,136.50 5.0% 58,085.21 6.4% 312.18 59,106.59
May-2018 82 59,136.50 709.20 247.95 60,093.64 5.0% 59,106.59 6.4% 317.30 60,133.09
Jun-2018 83 60,093.64 709.20 251.96 61,054.81 5.0% 60,133.09 6.4% 322.43 61,164.72
Jul-2018 84 61,054.81 709.20 255.99 62,020.00 5.0% 61,164.72 6.4% 327.59 62,201.51
Aug-2018 85 62,020.00 751.80 330.86 63,102.66 6.4% 62,201.51 6.4% 332.77 63,286.07
Sep-2018 86 63,102.66 751.80 336.64 64,191.10 6.4% 63,286.07 6.4% 338.58 64,376.45
Oct-2018 87 64,191.10 751.80 342.44 65,285.34 6.4% 64,376.45 6.4% 344.42 65,472.66
Nov-2018 88 65,285.34 751.80 348.28 66,385.42 6.4% 65,472.66 6.4% 350.29 66,574.75
Page 13 of 14
Monthly Annuities Cumulative Investment Return Calculation
Monthly Annuity Balance & Annual Return on Investment Calculations
Select Month Jul-2031 6.4%
Payment Opening Annuity Investment Closing Investment Opening Investment Investment Closing
Month Number Balance Amount Growth Balance Return % Balance Return % Growth Balance
Dec-2018 89 66,385.42 751.80 354.15 67,491.37 6.4% 66,574.75 6.4% 356.19 67,682.74
Jan-2019 90 67,491.37 751.80 360.05 68,603.23 6.4% 67,682.74 6.4% 362.12 68,796.66
Feb-2019 91 68,603.23 751.80 365.98 69,721.01 6.4% 68,796.66 6.4% 368.09 69,916.55
Mar-2019 92 69,721.01 751.80 371.94 70,844.75 6.4% 69,916.55 6.4% 374.09 71,042.44
Apr-2019 93 70,844.75 751.80 377.94 71,974.49 6.4% 71,042.44 6.4% 380.12 72,174.35
May-2019 94 71,974.49 751.80 383.97 73,110.26 6.4% 72,174.35 6.4% 386.18 73,312.33
Jun-2019 95 73,110.26 751.80 390.03 74,252.08 6.4% 73,312.33 6.4% 392.27 74,456.40
Jul-2019 96 74,252.08 751.80 396.12 75,400.00 6.4% 74,456.40 6.4% 398.40 75,606.60
Aug-2019 97 75,400.00 796.90 250.42 76,447.32 4.0% 75,606.60 6.4% 404.56 76,808.05
Sep-2019 98 76,447.32 796.90 253.90 77,498.12 4.0% 76,808.05 6.4% 410.18 78,015.13
Oct-2019 99 77,498.12 796.90 257.39 78,552.41 4.0% 78,015.13 6.4% 415.81 79,227.84
Nov-2019 100 78,552.41 796.90 260.89 79,610.20 4.0% 79,227.84 6.4% 421.47 80,446.22
Dec-2019 101 79,610.20 796.90 264.41 80,671.51 4.0% 80,446.22 6.4% 427.15 81,670.26
Jan-2020 102 80,671.51 796.90 267.93 81,736.34 4.0% 81,670.26 6.4% 432.84 82,900.00
Feb-2020 103 81,736.34 796.90 271.47 82,804.71 4.0% 82,900.00 6.4% 438.55 84,135.46
Mar-2020 104 82,804.71 796.90 275.01 83,876.62 4.0% 84,135.46 6.4% 444.29 85,376.64
Apr-2020 105 83,876.62 796.90 278.57 84,952.10 4.0% 85,376.64 6.4% 450.04 86,623.58
May-2020 106 84,952.10 796.90 282.15 86,031.14 4.0% 86,623.58 6.4% 455.81 87,876.29
Jun-2020 107 86,031.14 796.90 285.73 87,113.77 4.0% 87,876.29 6.4% 461.60 89,134.79
Jul-2020 108 87,113.77 796.90 289.33 88,200.00 4.0% 89,134.79 6.4% 467.41 90,399.09
Aug-2020 109 88,200.00 844.70 177.26 89,221.96 2.4% 90,399.09 6.4% 473.23 91,717.03
Sep-2020 110 89,221.96 844.70 179.31 90,245.98 2.4% 91,717.03 6.4% 478.72 93,040.44
Oct-2020 111 90,245.98 844.70 181.37 91,272.05 2.4% 93,040.44 6.4% 484.21 94,369.36
Nov-2020 112 91,272.05 844.70 183.43 92,300.18 2.4% 94,369.36 6.4% 489.72 95,703.77
Dec-2020 113 92,300.18 844.70 185.50 93,330.38 2.4% 95,703.77 6.4% 495.23 97,043.71
Jan-2021 114 93,330.38 844.70 187.57 94,362.66 2.4% 97,043.71 6.4% 500.76 98,389.17
Feb-2021 115 94,362.66 844.70 189.65 95,397.00 2.4% 98,389.17 6.4% 506.30 99,740.17
Mar-2021 116 95,397.00 844.70 191.73 96,433.43 2.4% 99,740.17 6.4% 511.85 101,096.72
Apr-2021 117 96,433.43 844.70 193.81 97,471.94 2.4% 101,096.72 6.4% 517.41 102,458.83
May-2021 118 97,471.94 844.70 195.90 98,512.53 2.4% 102,458.83 6.4% 522.98 103,826.51
Jun-2021 119 98,512.53 844.70 197.99 99,555.22 2.4% 103,826.51 6.4% 528.57 105,199.78
Jul-2021 120 99,555.22 844.70 200.08 100,600.00 2.4% 105,199.78 6.4% 534.16 106,578.64
Aug-2021 121 100,600.00 895.40 556.29 102,051.69 6.6% 106,578.64 6.4% 539.77 108,013.81
Sep-2021 122 102,051.69 895.40 564.32 103,511.41 6.6% 108,013.81 6.4% 547.56 109,456.76
Oct-2021 123 103,511.41 895.40 572.39 104,979.20 6.6% 109,456.76 6.4% 555.39 110,907.55
Nov-2021 124 104,979.20 895.40 580.51 106,455.11 6.6% 110,907.55 6.4% 563.26 112,366.21
Dec-2021 125 106,455.11 895.40 588.67 107,939.18 6.6% 112,366.21 6.4% 571.18 113,832.79
Jan-2022 126 107,939.18 895.40 596.88 109,431.45 6.6% 113,832.79 6.4% 579.14 115,307.34
Feb-2022 127 109,431.45 895.40 605.13 110,931.98 6.6% 115,307.34 6.4% 587.15 116,789.89
Mar-2022 128 110,931.98 895.40 613.42 112,440.80 6.6% 116,789.89 6.4% 595.20 118,280.49
Apr-2022 129 112,440.80 895.40 621.77 113,957.97 6.6% 118,280.49 6.4% 603.30 119,779.19
May-2022 130 113,957.97 895.40 630.16 115,483.53 6.6% 119,779.19 6.4% 611.44 121,286.03
Jun-2022 131 115,483.53 895.40 638.59 117,017.52 6.6% 121,286.03 6.4% 619.62 122,801.05
Jul-2022 132 117,017.52 895.40 647.08 118,560.00 6.6% 122,801.05 6.4% 627.85 124,324.30
Page 14 of 14