Formula for Total Rising Invoice Payments

Hi all,

Tearing my hair out here, particularly since I love numbers and Excel.

A company where I'm working charges a license fee annually that rises with a given rate.

So for example, in Y1 a fee of £58452, then in Y2 they'll invoice 58452 + 3.5%, Y3 they'll invoice Y2's invoice plus 3.5% and so on.

I can't for the life of me work out how to do work the total payable over the term in a formula, without simply running down a few lines and adding 3.5% to the previous line then summing them all.

The answer isn't a compound interest formula, nor the CUMIPMT function.

I think I'm getting close with the FV function, but that only gives me the final payment, not the total of all payments over the term.

HELP ! Please; do you know how to do this? Searching the forums haven't come up with the answer, nor wider internet searches.

Thanks in advance ! J

Hello
If for example the payments were over 10 years A2:A11, would something like the following formula return the correct value as you fill in A2:A11 with the 'Principal' amount which here is named in cell A2:

Formula:

Please Login or Register  to view this content.

DBY

Try

=A1*(1-r^n)/(1-r)

where

A1=principal

r=(1+Rate) e.g 1.035

n=number of periods

Hi all,

A genius colleague of mine worked it out, and it is this formula:

="START AMOUNT"*(1-POWER((1+"INFLATION RATE"),"NUMBER OF YEARS"))/(1-(1+"INFLATION RATE"))

which calculates the total payable over the term of a series of payments that rise incrementally by an inflation rate.

this also, of course, assumes that the start amount is what is paid in the first year, not the start amount plus the uptick/ RPI/ inflation rate that's applied.

Thanks John and DBY, but I think those are compounding formulae that return the total interest paid, which differs from the result I was after.

Silly me... yes, it is.. Thanks John!

Amazing how I spent a bit of time searching online for it, but using terms like 'inflation', 'interest' and 'total return' when I should have been searching for 'geometric progression' which, as you say, is what it's called.

I've learnt something today.. a few things!

Cheers, J