+ Reply to Thread
Results 1 to 6 of 6

Formula for Total Rising Invoice Payments

  1. #1
    Registered User
    Join Date
    09-26-2016
    Location
    Europe
    MS-Off Ver
    Mac 2011
    Posts
    3

    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

  2. #2
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Formula for Total Rising Invoice Payments

    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: copy to clipboard
    Please Login or Register  to view this content.


    DBY

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    18,397

    Re: Formula for Total Rising Invoice Payments

    Try

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

    where

    A1=principal

    r=(1+Rate) e.g 1.035

    n=number of periods

  4. #4
    Registered User
    Join Date
    09-26-2016
    Location
    Europe
    MS-Off Ver
    Mac 2011
    Posts
    3

    Re: Formula for Total Rising Invoice Payments

    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.

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    18,397

    Re: Formula for Total Rising Invoice Payments

    The formula I gave is exactly the same as you found.( SUM of a geometric progression)
    Attached Files Attached Files
    Last edited by JohnTopley; 09-26-2016 at 12:09 PM.

  6. #6
    Registered User
    Join Date
    09-26-2016
    Location
    Europe
    MS-Off Ver
    Mac 2011
    Posts
    3

    Re: Formula for Total Rising Invoice Payments

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Sum invoice payments without duplicates
    By kgkgkg9009 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-17-2016, 07:46 AM
  2. [SOLVED] Invoice template total column formula
    By Lilgnomey in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-28-2014, 08:39 AM
  3. Replies: 1
    Last Post: 06-12-2014, 10:58 AM
  4. Sum Payments on two worksheets after matching the invoice number
    By AMFISH in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-12-2014, 10:36 AM
  5. Replies: 2
    Last Post: 06-05-2013, 08:59 PM
  6. Replies: 2
    Last Post: 07-08-2009, 05:45 PM
  7. Help with formula for yearly rising cost spreadsheet please.
    By snowy2006 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-13-2007, 12:50 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1