The Student Room Group
Competition - post a photo you've taken of nature >>
Competition - post a photo you've taken of nature >>
Competition - post a photo you've taken of nature >>

This discussion is now closed.

Check out other Related discussions

Calculating Payback period in Excel

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
Reply 1
Avatar for thetroll
thetroll
8
Varying values of what for each year? Interest rate? Exchange rate? Repayment?
Reply 2
Avatar for Pamplemousse22
Pamplemousse22
OP
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
Reply 3
Avatar for thetroll
thetroll
8
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.
Reply 4
Avatar for alondonkid
alondonkid
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



:smile:
Reply 5
Avatar for deamer44
deamer44
payback period = total cost/(total revenue x years)

Related discussions

Latest

Trending

Trending

Articles for you