Solved

Excel - Calculating Payback period

Posted on 2016-07-29
8
104 Views
Last Modified: 2016-08-07
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?
0
Comment
Question by:gh_user
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 41734480
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
 

Author Comment

by:gh_user
ID: 41734507
Hi Saqib. I came up with the formula.  Might not be right.
0
 
LVL 4

Accepted Solution

by:
Alexandre Michel earned 500 total points
ID: 41734636
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 4

Expert Comment

by:Alexandre Michel
ID: 41737498
Hi GH

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

Cheers

Alexandre
0
 

Author Comment

by:gh_user
ID: 41743563
Thanks Alexandre.
Sorry..I missed seeing your answer.
It was great...well explained and tested!!
0
 
LVL 4

Expert Comment

by:Alexandre Michel
ID: 41743576
You are welcome!
If this answered your question, can close the question and rate the answer when you have 2 minutes
Thanks
0
 

Author Comment

by:gh_user
ID: 41743586
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
 

Author Closing Comment

by:gh_user
ID: 41746121
WELL EXPLAINED
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question