Compound interest with premium growing

[Simba]

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.

 

[Simba]

Ok, I have the following formula where:

i = interest rate
A = payment at beginning of period
n = number of payment periods
g = growth rate

(((1+i)^n-(1+g)^n)/(i-g))*A

Now this gives me the answer when I have only one payment per year(A) over a
number of years(n). It does not work if I want 12 payments per year, and the
growth ia anuually. If I change the number of payments to the number of
months, this formula will grow the premium each month by 5% which is not what
I want.

Any help with this please!

 

[Bernard Liengme]

Have a look in Help at the formula FV (Future Value) and also read Help on
PV which gives details on the parameters.

Here are some sites that may be helpful
http://www.eiu.edu/~dmcgrady/bus3710/assign/excel_functions.html
http://www.ehow.com/how_2095624_calculate-future-value-investment-excel.html
http://office.microsoft.com/en-us/excel/HP100698231033.aspx
http://office.microsoft.com/en-us/excel/HA011117451033.aspx

best wishes

 

[Joe User]

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 ----

 

[Joe User]

Potentially confusing typo, albeit unimportant....

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

That should be written $125,618.19.


----- original message -----

 

[Dana DeLouis]

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.

Hi. I think this is correct....

Sub Demo()
Debug.Print MyFv(150, 0.05, 0.08, 20)
End Sub

Function MyFv(n, gr, ir, yr)
Dim g, r, r12, k
g = 1 + gr
r = ir / 12
r12 = 1 + r
k = 1 - 1 / r12 ^ 12

MyFv = (n * k * r12 ^ 12 * (r12 ^ (12 * yr) - g ^ yr)) / (r * (r12
^ 12 - g))
End Function

Returns:
128660.8768

= = = = = = =
HTH :>)
Dana DeLouis

 

[Joe User]

Hi. I think this is correct.... [....]
Returns:
128660.8768

Well, it would be if premiums are paid at the end of each month.

But that does not make sense to me. I assume that premiums are paid at the
beginning of each month.

That is why I use 1 in the last argument of the inner FV
expression, -FV(B4,A5,premium,0,1), which computes the accumulation each
year's premium paid periodically over a year.

You can check your work and experiment with assumptions by using the
following paradigm.

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 set:

A7, initial premium: =A1
B7, annual accumulation: =FV(B$4,12,-$A7,0,1)
A8, subsequent premium: =A7*(1+$A$2)
B8, annual accumulation: =FV(B$4,12,-$A8,-B7,1)

Copy A8:B8 done through row 26 (20 years).

That emulates the computation in your UDF, with the payment-at-beginning
change.

But as I noted, it would be prudent to round A8 to 2 decimal places due to
real-world constraints. However, in that case, perhaps the formula in A8
should be ROUND($A$1*(1+$A$2)^ROW(A1),2). That depends on the OP's
requirements. It does not make much difference; but I believe if you're
gonna do something, y'might as well do it right.

(Note: ROW(A1) is a quick-and-dirty way to generate the exponent 1, 2, 3
etc as the formula is dragged down. Arguably, there are safer ways to do
it.)

Also as I noted, B4 might be calculated by =RATE(A5,0,-1,1+A4) if A4 is
actually the APY, not an annual interest rate. Again, that depends on the
OP's requirements.


As an aside....

Function MyFv(n, gr, ir, yr)
Dim g, r, r12, k

It is usually prudent to explicitly type variables.

"Dim g as Double" etc is more efficient.

"Function MyFv(...) as Double" is arguably better unless you want to return
errors with CVErr().


----- original message -----

 

[Dana DeLouis]

I assume that premiums are paid at
the beginning of each month.

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)))

Ok. Thanks. I switched it to payments at the beginning of each month
If interested, here is what I get.

Sub Demo()
Debug.Print MyFv(150, 0.05, 0.08, 20)
End Sub

Returns:
129518.616

Function MyFv(n, gr, ir, yr)
Dim g As Double
Dim r As Double
Dim w As Double
Dim k As Double

g = 1 + gr
r = ir / 12
w = 1 + r
k = 1 + r - 1 / w ^ 11

MyFv = (n * k * w ^ 12 * (w ^ (12*yr) - g^yr)) / (r * (w^12 - g))
End Function

 

[Joe User]

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)))

[....]
I switched it to payments at the beginning of each month
If interested, here is what I get. [....]
Returns:
129518.616

The difference -- my 129,518.70 (about) v. your 129,518.62 (about) -- is due
to rounding of the monthly payment, increased by 5% each year.

As I wrote in the "Notes" of my first response:

"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)."

I would be interested in the algebraic derivation of your formula.

I think I see one way to do it. But where I'm headed with it does not seem
to be as clean as yours. However, I do not have time right now to finish
the derivation and see if it "cleans up nicely".


----- original message -----

 

[Dana DeLouis]

Hi. Forgot to mention earlier...
Just having fun playing a little detective work here...

We will probably never hear from the OP, but when he mentioned the
following...

(((1+i)^n-(1+g)^n)/(i-g))*A

Now this gives me the answer when I have only one payment per year(A)
over a number of years(n).

The equation that he is saying "works" appears to be for payments that
are at the end of each period, and not at the beginning.
I may be wrong, but just thought I'd mention it. :>)

Dana DeLouis

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)))

[....]
I switched it to payments at the beginning of each month
If interested, here is what I get. [....]
Returns:
129518.616

The difference -- my 129,518.70 (about) v. your 129,518.62 (about) -- is
due to rounding of the monthly payment, increased by 5% each year.

As I wrote in the "Notes" of my first response:

"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)."

I would be interested in the algebraic derivation of your formula.

I think I see one way to do it. But where I'm headed with it does not
seem to be as clean as yours. However, I do not have time right now to
finish the derivation and see if it "cleans up nicely".


----- original message -----

Ok. Thanks. I switched it to payments at the beginning of each month
If interested, here is what I get.

Sub Demo()
Debug.Print MyFv(150, 0.05, 0.08, 20)
End Sub

Returns:
129518.616

Function MyFv(n, gr, ir, yr)
Dim g As Double
Dim r As Double
Dim w As Double
Dim k As Double

g = 1 + gr
r = ir / 12
w = 1 + r
k = 1 + r - 1 / w ^ 11

MyFv = (n * k * w ^ 12 * (w ^ (12*yr) - g^yr)) / (r * (w^12 - g))
End Function