"Simba" wrote:
Start premium per month - eg $150
Premium increase per year - eg 5%
Number of years - eg 20years
Interest rate per year - eg 8%

Suppose:

A1, initial period premium: 150
A2, annual premium increase: 5%
A3, number of years: 20
A4, annual interest rate: 8%
A5, number premiums per year: 12
B4, interest rate per period: =A4/A5 (format as Percentage)

Then, the future value (about $129,518.70) is:

=SUMPRODUCT(FV(B4,A5*(A3-ROW(INDIRECT("1:"&A3))),0,
-FV(B4,A5,-ROUND(A1*(1+A2)^(ROW(INDIRECT("1:"&A3))-1),2),0,1)))

See "Notes" for an explanation.


Notes:

1. If "interest rate" is really the APY (annual percentage yield), B4 should
be:

=RATE(A5,0,-1,1+A4)

It makes a significant difference (about $12,5618.19).

Note that if "interest rate" is the APY, the outer FV expression could be
simplified. Let me know if you need help with that, if you are interested.
The result will be the same either way.

2. The use of ROUND takes into account the real-world constraint that the
permium is paid in real currency. Not using ROUND makes a small difference
(about $129,518.62).

However, note that the appreciated premium is based on the initial premium,
not each rounded premium. Using the latter makes a small, but not
insignificant difference (about $129,521.73)

If you need the latter, I think the only way to compute that is with an
annual accumulation schedule. Let me know if you need help with that.

3. You could eliminate __both__ minus signs ("-") before FV and ROUND. That
is, either the minus sign must be before both FV and ROUND, or there must not
be a minus before both FV and ROUND. My use of the minus signs is a personal
preference.

4. Explanation of formula

The inner FV expression, -FV(B4,A5,premium,0,1), computes the accumulation
each year's premium paid periodically over a year.

The outer FV expression, FV(B4,numPeriods,,0,-FV(...)), computes the
appreciation of each year's ending balance over the remaining periods.

The use of SUMPRODUCT is one way to sum the outer FV experssions.
Alternatively, you could use SUM; but that would need to be an array formula.

The expression ROW(INDIRECT("1:"&A3)) is a trick to cause SUMPRODUCT (or
SUM) to iterate over 1 to A3, the number of years.


----- original message ----

"Simba" wrote:
Hi

I would like to know if there is a formula/function to calculate the
following:

Start premium per month - eg $150
Premium increase per year - eg 5%
Number of years - eg 20years
Interest rate per year - eg 8%

I would like to have the end result, or final total amount and the formula
or function to calculate it, thanx.