Results are out! Find what you need...fast. Get quick advice or join the chat
Hey! Sign in to get help with your study questionsNew here? Join for free to post

Calculating Payback period in Excel

Announcements Posted on
Will you get the grades you need for uni? Get prepared today and de-stress, sign up to email alerts for course places! 02-06-2015
Waiting on IB results? Our IB results hub explains everything you need to know 01-06-2015
  1. Offline

    ReputationRep:
    Hi,

    I'm trying to create a payback period in Microsoft Excel but I don't know how. I can't use the NPER function, because I have varying values for each year. Does anyone know how to do it?

    Thanks
  2. Offline

    ReputationRep:
    Varying values of what for each year? Interest rate? Exchange rate? Repayment?
  3. Offline

    ReputationRep:
    Oh sorry, varying values of income each year. Eg. Initial investment is 2,100,000, and the cash flows are Year1: 0, year2: 1,000,000, year3:1,500,000, year4:590,000, year5:800,000, etc
  4. Offline

    ReputationRep:
    OK, so you want to apply an interest rate to the remaining balance each year?

    So have a row for each year, with two columns, one for remaining balance, one for repayment made.

    The formula for each year's remaining balance is ((previous year's balance - previous year's repayment) * interest rate). Once the balance reaches zero, the loan is repaid.
  5. Offline

    ReputationRep:
    You can set up a template in MS Excel as shown in the following image
    Your answer is 2.73 years or in other terms 2 years and 9 months

    You can get the months by multiplying 0.73 times 12 = 8.76 rounded to 9



  6. Offline

    ReputationRep:
    payback period = total cost/(total revenue x years)

Reply

Submit reply

Register

Thanks for posting! You just need to create an account in order to submit the post
  1. this can't be left blank
    that username has been taken, please choose another Forgotten your password?
  2. this can't be left blank
    this email is already registered. Forgotten your password?
  3. this can't be left blank

    6 characters or longer with both numbers and letters is safer

  4. this can't be left empty
    your full birthday is required
  1. By joining you agree to our Ts and Cs, privacy policy and site rules

  2. Slide to join now Processing…

Updated: May 7, 2009
TSR Support Team

We have a brilliant team of more than 60 Support Team members looking after discussions on The Student Room, helping to make it a fun, safe and useful place to hang out.

New on TSR

Improving your uni offer

Why now is the time to think about Adjustment

x

Think you'll be in clearing or adjustment?

Hear direct from unis that want to talk to you

Get email alerts for university course places that match your subjects and grades. Just let us know what you're studying.

Quick reply
Reputation gems: You get these gems as you gain rep from other members for making good contributions and giving helpful advice.