You are Here: Home

Calculating Payback period in Excel Watch

Announcements
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)

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:
Updated: May 7, 2009
Today on TSR

What does it take to get into Oxbridge?

These personal statements nailed it!

Have I wasted my time at uni?

Discussions on TSR

• Latest
• 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
Useful resources

Can you help? Study Help unanswered threadsStudy Help rules and posting guidelines

Groups associated with this forum:

View associated groups
Discussions on TSR

• Latest
• 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

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