+ Reply to Thread
Results 1 to 9 of 9

Calculate total cost over time for a price that increases each month by a percentage

  1. #1
    Registered User
    Join Date
    06-04-2012
    Location
    UK
    MS-Off Ver
    Mac Excel 2011
    Posts
    5

    Calculate total cost over time for a price that increases each month by a percentage

    Hi,

    Spent most of the day sifting old forum threads for an answer to this question with no luck, so I thought it was about time I posted my own..

    I am in search of a formula that will give me to total cost over time (X nr of months) for a subscription, currently costing me Y, which increases each month by an given percentage (Z %).
    I have several of these, but to take one example:

    I have a subscription which will cost me 115 usd for July. I expect this subscription to increase each month by approximately 15% (compared with the previous month).
    So I'll expect a bill for 132 usd (115*1,15) in August, 152 (132*1,15) for September etc..

    What I need is a formula which will add these monthly costs up for me, so that I can get the total cost for 2nd half of 2012, for 2013 etc..

    Really appreciate your help with this
    Ollie

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Calculate total cost over time for a price that increases each month by a percentage

    Try this...
    A1: Starting mthly price....$115
    B1: Term in months..........6
    C1: Incremental Rate........0.15

    This regular formula returns the cumulative cost for those months:
    D1: =FV(C1,B1-1,-1,-1)*A1

    In the above example, the formula returns: $1,006.68 (technically: 1006.6799203125)
    EDITED TO INCLUDE THESE DETAILS:
    Please Login or Register  to view this content.
    Is that something you can work with?
    Last edited by Ron Coderre; 06-04-2012 at 08:06 PM.
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    06-04-2012
    Location
    UK
    MS-Off Ver
    Mac Excel 2011
    Posts
    5

    Re: Calculate total cost over time for a price that increases each month by a percentage

    That's works!!
    Anyway I can calculate the starting monthly price - so that I could have a formula for jan13-des13 too?

    Thanks a billion

  4. #4
    Registered User
    Join Date
    06-04-2012
    Location
    UK
    MS-Off Ver
    Mac Excel 2011
    Posts
    5

    Re: Calculate total cost over time for a price that increases each month by a percentage

    Hi again. I got some help from a friend of mine, recommending I use future_value(current-value;rate;no-months) to get my new "starting value" for jan13, jan14 etc.
    She sent me a spreadsheet (xlsm) with the calculations in it, but replicating it in my own produced an #NAME? error.
    I even copied her sheet into mine and that broke her formula, producing the same error.

    I'm working on a Mac and the spreadsheet ends with .xlsx.
    Should I be using a different formula due to my version of excel?

  5. #5
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Calculate total cost over time for a price that increases each month by a percentage

    Quote Originally Posted by ollienor View Post
    That's works!!
    Anyway I can calculate the starting monthly price - so that I could have a formula for jan13-des13 too?

    Thanks a billion
    Glad that part workeed for you.

    Can you post an example of your second request...sample data and the value(s) you want to see based on that data?

  6. #6
    Registered User
    Join Date
    06-04-2012
    Location
    UK
    MS-Off Ver
    Mac Excel 2011
    Posts
    5

    Re: Calculate total cost over time for a price that increases each month by a percentage

    Sure no problem. Give me a call when you arrive. I meet you outside the main exit.

    Manually calculating each month from july12 till des13:

    rate: 1,15
    july 115,00
    aug 132,25
    sept 152,09
    oct 174,90
    nov 201,14
    des 231,31 sum 2012 = 1007
    jan 266,00 starting value 2013 = 266
    feb 305,90
    ma 351,79
    april 404,56
    may 465,24
    june 535,03
    july 615,28
    aug 707,57
    sept 813,71
    oct 935,76
    nov 1076,13
    des 1237,55 sum 2013 = 7714,5
    ...

    starting value 2014 = 1423

    Your formula helps me calculate the accumulated cost for the next 6 months (i.e. 1007)
    But to calculate future periods (e.g. jan-des13), I need a way to find the starting value of that period (in this example: 266, or for 2014: 1423). This in order to get to my final accumulated price for that whole period (for 2013: 7714,5)

    Cheers,
    Ollie

  7. #7
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Calculate total cost over time for a price that increases each month by a percentage

    OK...I think I understand...You want to be able to calculate the sum of payments for any start/end period within the term.
    Pmts 1 through 6
    Pmts 7 through 18
    etc.

    If that's true, try this:
    A1: Rate
    A2: Initial Pmt
    A3: CalcMth_Start
    A4: CalcMth_End
    A5: PeriodSum

    B1: 0.15
    B2: $115
    B3: 7
    B4: 18

    This regular formula returns the sum of pmts for mths 7 through 18
    Please Login or Register  to view this content.
    In the above example, the formula returns: $7714.50

    If you change the parameters to calc mths 1 through 6
    the formula returns: $1006.68

    Does that help?

  8. #8
    Registered User
    Join Date
    06-04-2012
    Location
    UK
    MS-Off Ver
    Mac Excel 2011
    Posts
    5

    Wink Re: Calculate total cost over time for a price that increases each month by a percentage

    Perfect!!
    Thank you

  9. #9
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Calculate total cost over time for a price that increases each month by a percentage

    Quote Originally Posted by ollienor View Post
    Perfect!!
    Thank you
    You're very welcome....I had fun with this one.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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