• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 179
  • Last Modified:

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?
  • 4
  • 3
1 Solution
Saqib Husain, SyedEngineerCommented:
Using your formula I get

1.684488685 using 3


-0.527754004 using 3%

Can you show me where you got this formula from?

You can see details of Discounted Payback Period at

gh_userAuthor Commented:
Hi Saqib. I came up with the formula.  Might not be right.
Alexandre MichelManager; IT ConsultantCommented:

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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Alexandre MichelManager; IT ConsultantCommented:

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


gh_userAuthor Commented:
Thanks Alexandre.
Sorry..I missed seeing your answer.
It was great...well explained and tested!!
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
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.
gh_userAuthor Commented:
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.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now