Determining equal annual deposits to accumulate a future sum

Financial Management

In order to determine the size of equal, annual, end period deposits needed to accumulate a specific future sum at a specific future date, several steps are required. This type of equation involves working backwards from the solution that you wish to find. Thus, if you want to accumulate $2,000,000 in ten years, this is the end result from which the remainder of the equation will be derived.

The payments are annual, equal and end of period. This fits the definition of an annuity, so it is the annuity formula that we will use to derive the required interest rate in order to bring the payments to the end result of $2,000,000 in ten years. In this example, the interest rate is to be taken as a given, and the solution derived should therefore be the sole remaining unknown variable, the size of the payment. To derive this solution, an interest rate of 8% will be used.

The formula for an annuity is as follows:

where's is the future value of the annuity; R. is the periodic payment; n is the number of payments and I is the interest rate. Plugging in the values given, the formula would be:

2,000,000 = R [((1.08)^10 -- 1)/.08]

From this point, the bracket beside R. can be derived into a simpler numeral.

(1.08)^10 = 2.15892

2.15892 -1 = 1.15892

1.15892 / .08 = 14.4865

This leaves the original formula as follows:

2,000,000 = R (14.4865)

From which we can derive R, the periodic payment:

2,000,000 / 14.4865 = 138,059

There are a couple of important things to remember about this formula. The first is that the last principle payment, at the end of year 10, is included in the $2,000,000, but it does not receive interest. The formula therefore equates to the previous years' ending principle balance multiplied by 1.08, to which the current year's new principle is added. Thus, the first payment receives 1.08^10 as an interest multiplier, the second payment receives 1.08^9, down to the last payment, which receives no interest multiplier.

These figures can be calculated in Excel as well. Each principle payment will be entered into the spreadsheet. The end value will be calculated as the previous period's principle * 1.08, to which the new principle payment is added. The final value, and the end of year 10, should be 2,000,000.

The Solver tool can be used to calculate the annual, equal, end of term payment. The target value is set for the end value cell for year ten at 2,000,000. The first cell, for the initial payment, should be the cell that is changed to reach the solution. Excel delivers a solution of 138,059, which is the same as the solution that was derived from the annuity formula.