Solved

Excel - Calculating Payback period

Posted on 2016-07-29
8
81 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
  • 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
ScreenConnect 6.0 Free Trial

Want empowering updates? You're in the right place! Discover new features in ScreenConnect 6.0, based on partner feedback, to keep you business operating smoothly and optimally (the way it should be). Explore all of the extras and enhancements for yourself!

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

821 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