We help IT Professionals succeed at work.

# Excel - Calculating Payback period

on
Im trying to use excel to assess a solar panel quote given to me.
They have given me the cost of the panel.  I can calculate the savings after 1 year based on the current tariff (electricity price per kwh usage).  The tariff is expected to increase per year (based on index)
A need a excel formula that can calculate the number of years until the total savings match the upfront cost of the panel.
Its based on cost = savings/year (based on current tariff) x (1 + %annual increase in tariff) to the power of number of years)
Currently the variables are:
C = Cost = \$7000
S = Savings/year (based on current tariff) = \$1100
I = Index based on %annual increase in tariff = 3%

But my formula No. of years = LOG(C/S,I) is getting strange answer of 63.

Any suggestions?
Comment
Watch Question

## View Solution Only

Commented:

1.684488685 using 3

and

-0.527754004 using 3%

Can you show me where you got this formula from?

You can see details of Discounted Payback Period at

http://accountingexplained.com/managerial/capital-budgeting/discounted-payback-period

Commented:
Hi Saqib. I came up with the formula.  Might not be right.
Manager; IT Consultant
Commented:
Hi GH

The formula is Log((1+i*R)/(1+i))/log(1+i)
where i= 0.03
and R = 7000/1100 = 6.36
In this case, the answer is 4.9 years

I decided to test it by doing the calculations one step at a time

To make sure my formula was correct, we can check how long we would need to repay a system costing \$9781.57 ... The answer should be 7 years

Attached is the spreadsheet if you want to play with the values

Hope this is what you were after

Alex
Calculations.xlsx
Manager; IT Consultant

Commented:
Hi GH

Cheers

Alexandre

Commented:
Thanks Alexandre.
It was great...well explained and tested!!
Manager; IT Consultant

Commented:
You are welcome!
If this answered your question, can close the question and rate the answer when you have 2 minutes
Thanks

Commented:
Hi Alexandre.  I cant seem to close question.  (I have been able to rate your answer as best solution).  I think the problem is due to question being abandoned due to my delay in responding.  I have put in request to get rectified.

Commented:
WELL EXPLAINED