x

## Unlock these great extras with your FREE membership

• Customise TSR to suit how you want to use it

# Calculating Payback period in Excel

Announcements Posted on
Rate your uni — help us build a league table based on real student views 19-08-2015
1. 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. Varying values of what for each year? Interest rate? Exchange rate? Repayment?
3. 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. 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. 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. payback period = total cost/(total revenue x years)

## Register

Thanks for posting! You just need to create an account in order to submit the post
1. this can't be left blank
2. this can't be left blank
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
1. By joining you agree to our Ts and Cs, privacy policy and site rules

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.

This forum is supported by:
Today on TSR

### Win a mini-fridge

Don't miss our Freshers competition!

Poll
Useful resources
Study resources
x