Hey there! Sign in to join this conversationNew here? Join for free

Calculating Payback period in Excel Watch

Announcements
    • Thread Starter
    Offline

    0
    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
    Offline

    0
    ReputationRep:
    Varying values of what for each year? Interest rate? Exchange rate? Repayment?
    • Thread Starter
    Offline

    0
    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
    Offline

    0
    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.
    Offline

    0
    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



    Offline

    0
    ReputationRep:
    payback period = total cost/(total revenue x years)
 
 
 
  • See more of what you like on The Student Room

    You can personalise what you see on TSR. Tell us a little about yourself to get started.

  • Poll
    Should Spain allow Catalonia to declare independence?
  • See more of what you like on The Student Room

    You can personalise what you see on TSR. Tell us a little about yourself to get started.

  • The Student Room, Get Revising and Marked by Teachers are trading names of The Student Room Group Ltd.

    Register Number: 04666380 (England and Wales), VAT No. 806 8067 22 Registered Office: International House, Queens Road, Brighton, BN1 3XE

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