Sorry for missing this question.

I don't think you can have one formula for all cells in the SIP table because your first FV formula has the type argument set to 1 (meaning payments are due at the beginning of the period), whereas your remaining formulas have type omitted, which is reverts to type 0 (meaning payments are due at the end of the period).

You could do something with helper cells to populate the rate and nper values so the formula referenced cells instead of having the calculation nested in the formula. This way the differences in your formulas would be performed outside of the formula itself.

Hope that makes sense and helps point you in the right direction.

Mynda

Hi,

Just so I have a handle on your situation, please confirm, correct or add to the following:

1          payments are made each month, into perpetuity (let’s call that 10 years for the sake of calculation).  Therefore, payments made in the 1^st year will be 60,000 (5,000 per mth), 2^nd year 66,000 (5,500 per mth), 3^rd year 72,600 (6,050 per mth), and so on. That makes the total amount invested in the order of 956,245.48.

2          the original years investment (60,000 + whatever interest is earnt) will form the basis of the starting PV for the next year

3          no deductions will be made over the years

4          interest will be paid monthly for each new year (ie – increased investment amount), but paid annually for subsequent years

 Now, a couple of questions...

1          will interest be paid at the beginning of the period, or the end?  Or is it different between each new year versus the previous year(s)?  If different, why?

2          the calculation of the average annual return assumed for each new years amount is different to that of previous years.  Is that due to an annual commission or fee? Is so, what is it / how is it defined?

For example

for each new year the calculation is ((1+Sheet1!$E$8)^(1/12)-1) = 0.9488792935% per period (month)

for subsequent years the calculation is (Sheet1!$E$8) = 12.00% per period (year)

If the method of calculation per period was to be equitable between new & previous years, the first calculation would be (Sheet1!$E$8/12) = 1.00% per period (month)

Let me know if anything is unclear.

cheers, Craig

Thanks Mynda and Craig for taking your valuable time out to help me.

Let me clarify:

1st year 12 instalments of Rs. 5000 each was invested at start of every month. I found the FV of these 12 instalments after 1 year using FV (Rate, Nper, PMT,,Type). As I have been given annual average return that is why I am writing Rate as ((1+Sheet1!$E$8)^(1/12)-1)

But after 1 year this particular FV becomes PV for subsequent years and the formula used is FV (Rate, Nper,,PV,) 

So at the end of 2 years FV consists of 

FV of first year end total value 

+

FV of second year's 12 instalments of each Rs. 5,500.

This is a typical case of graduated annuities but with a difference as here monthly annuities change after every 12 months.

[Rs. is short form of Indian currency Rupees.]

I got some nice insights from here  http://www.tvmcalcs.com/index.php/calculators/apps/excel_graduated_annuities

May be you can also refer the above link to understand it better. 

Hi amisandip.

I'm having trouble with a couple of things...

1/  why do you calculate the periodic interest rate for each new set of payments as (1+Sheet1!$E$8)^(1/12)-1   1.12^0.83333-1 = 0.00948879 (0.948879%)

Why not just use 0.12/12 (ie 1.0000%)?

As the FV calculated here flows on, any error is magnified.  Therefore it must be correct.

2/  As Mynda states, you are mixing when interest is paid.  For each new set of payments you want the interest paid at the beginning of the period (basically paying interest as soon as the payment is made).  For subsequent years - using the already calculated FV - you want the interest paid at the end of the period.  It doesn't make sense to me.  Why would they not be the same?  Are there more rules of which we should be aware?

I've done some calculations using interest paid at end of period (for all calculations) and the difference between your 10 year figure & mine is 10,753.  Quite significant.

When finding FV of an annuity we are supposed to follow this i.e. RATE = (1+ Annual Return)^(1/12)-1 instead of (Annual Return / 12) as this gives more accurate result. Though the actual mathematical reason behind this if you ask me, I am clueless 🙁 You can also refer to this link  http://msofficeworld.com/future-value-of-annuity-formula/