Excel Gurus

Author

Previous Topic | Next Topic

Scrooge McDuck

8851 Posts

Posted - 05/13/2008 : 05:25:17 AM

i need a formula that shows compounding interest that works with an increasing principal amount. every formula i know, and all that i find on google are for 1 static principla amount. i want to be able to show a client how it grows when you keep adding to your savings with the interest compounding on top of it.

i want to be able to show, for example...

year 1 deposit $5,000 in an account with interest of 6%, so the interest built up after a year would be $5,300. year 2 savings is $5000 again, on top of the $5,300 plus the interest would be $10,918, and so on...

any excel gurus have this forumla for me that they can post up?

3019 Posts

Posted - 05/13/2008 : 05:38:28 AM

quote:
Originally posted by Scrooge McDuck

i need a formula that shows compounding interest that works with an increasing principal amount. every formula i know, and all that i find on google are for 1 static principla amount. i want to be able to show a client how it grows when you keep adding to your savings with the interest compounding on top of it.

i want to be able to show, for example...

year 1 deposit $5,000 in an account with interest of 6%, so the interest built up after a year would be $5,300. year 2 savings is $5000 again, on top of the $5,300 plus the interest would be $10,918, and so on...

any excel gurus have this forumla for me that they can post up?

=PV*(1+R)^N

PV is present value, R is the interest rate, and N is the number of investment periods

JoefromPhilly

661 Posts

Posted - 05/13/2008 : 05:49:31 AM

I validated ML's formula for you. It is correct.

3019 Posts

Posted - 05/13/2008 : 06:00:20 AM

quote:
Originally posted by JoefromPhilly

I validated ML's formula for you. It is correct.

780 SAT, 450 FICO

Scrooge McDuck

8851 Posts

Posted - 05/13/2008 : 06:01:24 AM

ok, sorry, but how do i set that up in excel?

3019 Posts

Posted - 05/13/2008 : 06:06:40 AM

using your example:

=5000*(1+.06)^2

each time you change the desired outcome you will have to manually change the formula you enter in an individual cell

JoefromPhilly

661 Posts

Posted - 05/13/2008 : 08:12:45 AM

Actually, that formula does not work. It is only for compounding the initial investment!

Let me figure this out for you.

JoefromPhilly

661 Posts

Posted - 05/13/2008 : 08:22:48 AM

Here you go:
n Excel, the FV function returns the future value of an investment based on an interest rate and a constant payment schedule.

The syntax for the FV function is:

FV( interest_rate, number_payments, payment, PV, Type )

interest_rate is the interest rate for the investment.

number_payments is the number of payments for the annuity.

payment is the amount of the payment made each period.

PV is optional. It is the present value of the payments. If this parameter is omitted, the FV function assumes PV to be 0.

Type is optional. It indicates when the payments are due. Type can be one of the following values:

Value Explanation
0 Payments are due at the end of the period. (default)
1 Payments are due at the beginning of the period.

If the Type parameter is omitted, the FV function assumes a Type value of 0.

Let's take a look at a few examples:

This first example returns the future value of an investment where you deposit $5,000 into a savings account that earns 7.5% annually. You are going to deposit $250 at the beginning of the month, each month, for 2 years.

=FV(7.5%/12, 2*12, -250, -5000, 1)

This next example returns the future value of an investment where you deposit $8,000 into a savings account that earns 6% annually. You are going to deposit $50 at the end of the week, each week, for 4 years.

=FV(6%/52, 4*52, -50, -8000, 0)

This next example returns the future value of an investment where you deposit $6,500 into a savings account that earns 5.25% annually. You are going to deposit $100 at the end of the year, each year, for 10 years.

=FV(5.25%/1, 10*1, -100, -6500, 0)

This is the correct method to do this!

- Joe

JoefromPhilly

661 Posts

Posted - 05/13/2008 : 08:28:34 AM

Here it is in your example:
=FV(6%, N, -5000, 0, 1)

Replace "N" with the number of years for the investment to run.

CoralSnake

10884 Posts

Posted - 05/13/2008 : 08:28:57 AM

huh?

JoefromPhilly

661 Posts

Posted - 05/13/2008 : 08:35:08 AM

To show this, if you do it manually over 4 years, starting with a zero investment and $5,000 annual savings, adding $5,000 each year:

Year Savings Investment Rate Value
1 $5,000 $0 6.00% $5,300
2 $5,000 $5,300 6.00% $10,918
3 $5,000 $10,918 6.00% $16,873
4 $5,000 $16,873 6.00% $23,185
5 $5,000 $23,185 6.00% $29,877

At the end of 5 years, you have $29,877.
Using the formula of [=FV(6%,5, -5000, 0, 1)], you get: $29,876.59 !

Proof complete :-)

JoefromPhilly

661 Posts

Posted - 05/13/2008 : 08:42:22 AM

All you do is enter the FV formula into a cell, and use the number of years you want to show the investment to run.

Scrooge McDuck

8851 Posts

Posted - 05/13/2008 : 5:41:00 PM

thank you for your help joe.

FLProcessor

429 Posts

Posted - 05/13/2008 : 7:19:23 PM

Yes, but if you want a dynamic investment amount (add/deduct an extra $5k next year, etc), you must do a search for an IRR function that takes a list of multiple payments.

mantixmortgage

2717 Posts

Posted - 05/13/2008 : 8:05:28 PM

excel account executives?

JoefromPhilly

661 Posts

Posted - 05/14/2008 : 06:31:14 AM

Actually, creating a spreadsheet that does a variety of payments over the years does not need the IRR function. This is used to get the internal rate of return. If you know the estimated return for the analysis purposes, all you need to do is to create a spreadsheet that has a column for the current year's investment, one for the total to-date investment, one for the investment return rate each year, and then one that takes the current year's investment, adds it to the portfolio value, and then multiplies it by 1.x, where "x" is the return rate for the year. The result of this gets carried to the next line of column 2. It's not that hard to do. You can also set up a section that has the variables such as initial investment, annual investment and investment return rate, then just call these variables in the formulas. This way, you can show the client what the impact would be if rates went up or down, and you can create a graph that shows the growth of the portfolio over the years. Best of all, if you have Excel, you don't have to purchase anything else.

- Joe

JoefromPhilly

661 Posts

Posted - 05/14/2008 : 06:32:03 AM

Oh, Scrooge, you're welcome. Glad I could help!

- Joe

Previous Topic | Next Topic