PayBack Period

[Scotty]

I hope I can make this understandable!

I am looking for a formula that enables me to determine the pay back period
on an investment project. Relevant input will be the initial investment cost,
monthly income generated by this investment (rising annually by CPI) and an
allowance for 'cost of money' being the 'Discount factor'. The answer should
be a period of months.

I can calculate this (a little manually) but need a formula so that I can
carry out multiple calculations on a range of scenarios. I know this is
probably very simple but I am no Excel expert.

Hope someone can assist.

Cheers...Scotty

 

[Fred Smith]

If you have constant monthly income, then you have a simple NPER formula:
=nper(DiscountRate/12,MonthlyIncome,-InvestmentAmount)

With increasing monthly income, one common solution is to use the real rate
of return, as in:
=nper((DiscountRate-CPI)/12,InitialMonthlyIncome,-InvestmentAmount)

If you don't want to use the real rate of return approach, then you can
automate your method using Goal Seek.

Regards,
Fred.

 

[Scotty]

Fred,

many thanks for this and for making it so simple to understand.

Cheers...>Scotty