[Pamplemousse22]

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

[thetroll]

Varying values of what for each year? Interest rate? Exchange rate? Repayment?

[Pamplemousse22]

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

[thetroll]

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.

[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

[deamer44]

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