Excel - Calculating Payback period

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?
gh_userAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
Using your formula I get

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
0
gh_userAuthor Commented:
Hi Saqib. I came up with the formula.  Might not be right.
0
Alexandre MichelManager; IT ConsultantCommented:
Hi GH

I had fun solving your puzzle!

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

Total saving after ~5 years
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

 Savings after 7 years
Attached is the spreadsheet if you want to play with the values

Hope this is what you were after

Alex
Calculations.xlsx
2

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Alexandre MichelManager; IT ConsultantCommented:
Hi GH

Have I answered your question?
If I have, please marked the question as "Answered". Thanks

Cheers

Alexandre
0
gh_userAuthor Commented:
Thanks Alexandre.
Sorry..I missed seeing your answer.
It was great...well explained and tested!!
0
Alexandre MichelManager; IT ConsultantCommented:
You are welcome!
If this answered your question, can close the question and rate the answer when you have 2 minutes
Thanks
0
gh_userAuthor 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.
0
gh_userAuthor Commented:
WELL EXPLAINED
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.