0

I have the income X, and the number of years in the future Y, and the inflation Z. I can calculate the compound interest simply by CI(Y) = X*(1+Z)^Y for any given year, but how can I construct a formula that adds each years income together for a total sum?

Ex)

X = 1000
Y = 3
Z = 5%
CI(0) = $1000
CI(1) = $1050
CI(2) = $1102,5
CompoundInterestSum(3) = $3152,5

I could do this in code recursively, but I'd like to know the cleaner formula that I can use in Excel. Thanks!

  • There are a lot of formulas -> financial in excel that can accomplish what you desire. However, it looks like CI(1) should be 1050, etc. In otherwords, C(0) should be 1000. Also, your calculation is the principal and interest. therefore your CompoundInterestSum(3) is overstated. If you think not, then I would like to invest in your company... – gns100 Dec 12 '18 at 17:48
  • Thanks! You are correct and I've updated to start at a zero-based index. What do you mean by overstated in the example? My actual scenario is calculating total returns on solar power. – jsmars Dec 13 '18 at 10:52
1

=-FV(z,y,x) or =FV(z,y,-x)

This calculates the Future Value of a stream of amounts based on the interest rate per period, the number of periods, and the amount added each period.

https://support.office.com/en-us/article/FV-function-2EEF9F44-A084-4C61-BDD8-4FE4BB1B71B3

  • 1
    I was looking at this function earlier, but it returns a negative value and it's described as used for loans. Looking into it further it seems the result is the same as long as the value is negated, so if you updated your answer with a - or *-1 for clarity I will mark it as correct. Thanks a lot! – jsmars Dec 13 '18 at 10:47
  • @jsmars, that's strange that it comes out negative. Apparently it has to do with tradition with how finance people think of (+) and (-)... good catch, I'll update – BobtheMagicMoose Dec 13 '18 at 19:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.