Solved

Excel - Calculating Payback period

Posted on 2016-07-29
8
114 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

626 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