Excel formula for sum of income over multiple years adjusted for inflation

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!

jsmars

Posted 2018-12-12T17:20:52.440

Reputation: 165

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 – 2018-12-12T17:48:43.477

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 – 2018-12-13T10:52:44.863

1I 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 – 2018-12-13T10:47:18.880

@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 – 2018-12-13T19:45:51.663

Excel formula for sum of income over multiple years adjusted for inflation

Answers