Looking for Excel Spreadsheet Expert

RacingDriver

Apodemus wrote: »

Would the "future value" function not do this for you?

FV(rate, nper, pmt, [pv],[type])

I think the FV function only works with a fixed value each month rather than the value increasing each month

ruperts

Definitely in the realm of Excel 'tricks' here then because as far as I'm aware there's not a simple mathematical or excel formula capable of dealing with increasing contributions.

Essentially I think you're going to have to cycle through each month individually somehow. You might be able to write a massive formula that does this (ie one monthly calculation, repeated over and over again with the different contributions drawn from multiplying a reference cell), but that would be so long winded i'm sure it would defeat the object.

You might also be able to do something with iterative calculations, which will allow you to increase the monthly contribution within one cell and then set the number of iterations to the number of months you want to run it for. I'd need to play around with it to see exactly how that would work though.

Other than that you're definitely in macro territory I believe.

You could always cheat by having a hidden data sheet in the background and then draw the key bits of information forward into a presentable summary sheet.

Apodemus

ruperts wrote: »

.You could always cheat by having a hidden data sheet in the background and then draw the key bits of information forward into a presentable summary sheet.

Nothing cheating about that, it's often the most sensible way show clear output from complex spreadsheets.

RacingDriver

ruperts wrote: »

mathematical or excel formula capable of dealing with increasing contributions

(n(n+1) / 2) X i works (where n is months and i is amount)

e.g. 3 months starting at £100 and increasing by the same amount each month = (3(3+1) / 2) X £100 = (3(4) / 2) X £100 = 12/2 X £100 = 6 X £100 = £600

which is the same as £300 + £200 + £100 = £600

bbeat65

RacingDriver wrote: »

Try this =((C1*(C1+1))/2)*A1*(1+B1/12)

where A1 = Monthly Amount, B1 = Annual Interest Rate and C1 = Number of Months

Put formula in D1 to give result

Thank you very much! I think that does it. The way I tried using a row for every month was close, but there is a flaw. It came out 5.2 mil (over 240 months) instead of 5.7mil. I feel like it is in the way I have it compounding (monthly vs annual, or something) Here is the (simple) formula that I used. I just did 1 row for 240 months, 1 for 239 months, one row for 238, etc. If you have a moment, since you seem to really understand this stuff, could you reply, with what I did to have it come out different?

A B C D E F
1 8% 240 -100 $58,902.04
2 8% 239 -100 $58,412.62
3 8% 238 -100 $57,926.45

A Row number only
B interest rate
C number of months the $100 is invested
D amount invested ($100)
E I didnt enter any value for E

Formula =FV(B8/12,C8,D8,E8)

I still look at your formula, and have a tough time "getting it". But, I sure do appreciate the ones, like you, who do. I have a question for you too - how long did it take you to figure that out? Just curious

RacingDriver

bbeat65 wrote: »

Thank you very much! I think that does it. The way I tried using a row for every month was close, but there is a flaw. It came out 5.2 mil (over 240 months) instead of 5.7mil. I feel like it is in the way I have it compounding (monthly vs annual, or something) Here is the (simple) formula that I used. I just did 1 row for 240 months, 1 for 239 months, one row for 238, etc. If you have a moment, since you seem to really understand this stuff, could you reply, with what I did to have it come out different?

Month # Interest Rate Months Monthly Deposits Initial Deposit Total/Mo at end
1 8% 240 -100 $58,902.04
2 8% 239 -100 $58,412.62
3 8% 238 -100 $57,926.45

Formula =FV(B8/12,C8,D8,E8)

I still look at your formula, and have a tough time "getting it". But, I sure do appreciate the ones, like you, who do. I have a question for you too - how long did it take you to figure that out? Just curious

I don't think the FV function works for this as FV only works with a fixed payment made each period, whereas you want it to increase each month.

All my formula is doing is working out the addition of the number of months. So 12 months gives 12+11+10+9+8+7+6+5+4+3+2+1=78. Then from there it times 78 by £100 (A1), to give £7,800 and then it adds the monthly interest which is B1/12 (assuming B1 is the annual interest rate). I have included 1+B1 in the formula as otherwise the result would just be the amount of interest earnt in the month, minus the original deposits.

In answer to how long it took me to figure out, not long... I new that n! gives increase number multiplied e.g. 3! = 3 X 2 X 1 so a quick google found the formula for additional increases, 3+2+1

ruperts

I'm not sure that formula works tbh. It seems to only add interest once, on the total amount contributed, whereas I think it should be adding interest at each period.

Based on 240 months at 8% per annum with contributions rising by £100 each month I get a total of £5.329m doing it manually. Whereas RacingDrivers formula only gives me £2.911m.

bbeat65

ruperts wrote: »

I'm not sure that formula works tbh. It seems to only add interest once, on the total amount contributed, whereas I think it should be adding interest at each period.

Based on 240 months at 8% per annum with contributions rising by £100 each month I get a total of £5.329m doing it manually. Whereas RacingDrivers formula only gives me £2.911m.

Hmmm! When I did it manually, I came up with 5.294M, and when I used RacingDriver's it came up with 5.784M. Funny how that works. I like RacingDrivers formula, and will use it. What I am using it for does not require any specific compounding, or anything. I think that must be where I went wrong, or where we differ. It's more the concept I am looking for. Thank you two for your help, and feedback. I am sure some day, I will become well versed in Excel too. And, maybe I will figure out how or why the numbers differ slightly.

Have a great day, or night I guess! (I believe its about 10:30pm there).

bbeat65

I agree RacingDriver. That's why I had to have 240 rows to come up with the number 5.294 million (or whatever it was). Which was still a bit off from yours. I am not sure why. Your formula, and work is much appreciated, and I will use it. It's much simpler to use, once it's been entered. Thank you

bbeat65

bbeat65 wrote: »

Hmmm! When I did it manually, I came up with 5.294M, and when I used RacingDriver's it came up with 5.784M. Funny how that works. I like RacingDrivers formula, and will use it. What I am using it for does not require any specific compounding, or anything. I think that must be where I went wrong, or where we differ. It's more the concept I am looking for. Thank you two for your help, and feedback. I am sure some day, I will become well versed in Excel too. And, maybe I will figure out how or why the numbers differ slightly.

Have a great day, or night I guess! (I believe its about 10:30pm there).

OOPS! Maybe I spoke too soon. I just noticed that I had 12% in RacingDrivers Formula. It came out to 4.82M after 240 months.