Interest rate question

  • Thread starter Thread starter Kyle P.
  • Start date Start date
K

Kyle P.

Hello,
I need to determine how much money a solar array will save one of my
customers over the next thirty years.

I'd like to know the formula to calculate this.

Inputing: 17 Cents per kWh.
21,128 kWh's per year
a 5% increase in the cost of electricity per year. (17cents
per kWh increasing by 5% every year).
over 30 years.

Help Please! Thanks.
 
Kyle P. said:
I need to determine how much money a solar array
will save one of my customers over the next thirty years.
Click to expand...

=FV(5%,30,-0.17*21128,0)

You can check this by changing 30 to 3 and computing the following 3-year
formula:

=21128*(0.17 + 0.17*(1+5%) + 0.17*(1+5%)^2)


----- original message -----
 
You need to know the intrest rate on the money to buy the solar array (even
if the customer is now borrowing there is a time value to money). Lets say
the customer borrows $20,000 @ 8% on Jan 1 2010 to purchase the array. Now
assume that he normally pays his electric bill on Jan 1 each year (money he
now gets to save). You can use XNPV (must install the analysis toolpack) to
compute a savings of $3,618.23

here is the source data with 8% in A1 and the table starting a A3.
My formula is
=XNPV(A1, B3:B33, A3:A33)

8%

01/01/10 -20000 3618.230141
01/01/11 1242.823529
01/01/12 1304.964706
01/01/13 1370.212941
01/01/14 1438.723588
01/01/15 1510.659768
01/01/16 1586.192756
01/01/17 1665.502394
01/01/18 1748.777514
01/01/19 1836.216389
01/01/20 1928.027209
01/01/21 2024.428569
01/01/22 2125.649998
01/01/23 2231.932497
01/01/24 2343.529122
01/01/25 2460.705578
01/01/26 2583.740857
01/01/27 2712.9279
01/01/28 2848.574295
01/01/29 2991.00301
01/01/30 3140.55316
01/01/31 3297.580818
01/01/32 3462.459859
01/01/33 3635.582852
01/01/34 3817.361995
01/01/35 4008.230095
01/01/36 4208.641599
01/01/37 4419.073679
01/01/38 4640.027363
01/01/39 4872.028732
01/01/40 5115.630168
 
Are you sure that is correct. FV is normally used to calculate the future
value of an investment. That is if I put money into a retirement investment
every year for the next 20 years with the interest compunding how much money
will I have to retire on. That does not seem to apply to this situation.
 
Sorry about that. My formula for the energy cost per year was wrong... Here
is the corrected calculation... It is the same XNPV formula.

8%

1/1/2010 -20,000.00 48,256.69
1/1/2011 3,591.76
1/1/2012 3,771.35
1/1/2013 3,959.92
1/1/2014 4,157.91
1/1/2015 4,365.81
1/1/2016 4,584.10
1/1/2017 4,813.30
1/1/2018 5,053.97
1/1/2019 5,306.67
1/1/2020 5,572.00
1/1/2021 5,850.60
1/1/2022 6,143.13
1/1/2023 6,450.28
1/1/2024 6,772.80
1/1/2025 7,111.44
1/1/2026 7,467.01
1/1/2027 7,840.36
1/1/2028 8,232.38
1/1/2029 8,644.00
1/1/2030 9,076.20
1/1/2031 9,530.01
1/1/2032 10,006.51
1/1/2033 10,506.83
1/1/2034 11,032.18
1/1/2035 11,583.78
1/1/2036 12,162.97
1/1/2037 12,771.12
1/1/2038 13,409.68
1/1/2039 14,080.16
1/1/2040 14,784.17
 
That won't be the amount saved. That would be an estimated cost of
electricity. $238632.39

Column a is 21,128
B1 is .17
B2 is B1*1.05
copy down
C1 is A1*B1
copy down
Sum C1:C30

Savings would that sum minus what it cost him to get it using a Solar
array including purchase costs, maintenance cost, battery costs and
replacements for power continuity, any backup power charges from the
utility etc.
 
Jim Thomlinson said:
Are you sure that is correct.
Click to expand...

Yes; and you could confirm that yourself with less than 60 seconds of
effort.

But first, you need to understand my interpretation of the problem for which
I provided a solution. I believe that Kyle is asking: what is the sum of
the cost (or cost saving?) over 30 years if the cost (or cost saving?) the
first year is $0.17 times 21,128 kWh, and the cost (or cost saving?)
increases 5% each year.

If you disagree that that is what Kyle asked for, fine. We simply have a
difference of opinion of the definition of the problem. If you disagree
with Kyle that that's the problem to solve, that's another matter
altogether. I am simply saying that FV solves my interpretation of Kyle's
problem.

To demonstrate....

Let A1 be the cost the first year: =21128*0.17. Let A2 be the cost the
second year: =21128*0.17*(1+5%). But that's just: =A1*(1+5%). Let A3 be
the cost the third year: =21128*0.17*(1+5%)*(1+5%). But that's just:
=A2*(1+5%). So drag A2 down through A30. Then =SUM(A1:A30) is the total
cost (or cost saving?) over 30 years.

Now compare that SUM with FV(5%,30,-0.17*21128,0). QED.

FV is normally used to calculate the future value of an investment.
Click to expand...

Fred is better at explaining the concepts behind this. I can only explain
the algebra.

Consider an investment of P dollars at the end of each year, with a growth
rate of 5%. At the end of the second year, the investment value is
P*(1+5%)+P. At the end of the third year, the value is (P*(1+5%)+P)*(1+5%),
which is P*(1+5%)^2 + P*(1+5%) + P. At the end of thirty years, the value
is P*(1+5%)^29 +...+ P*(1+5%) + P. FV(5%,30,-P,0) is the result of the sum.

Now consider my interpretation of Kyle's problem. The cost (or cost
saving?) in the first year is P, where P=kWh*$0.17. The cost in the second
year is P*(1+5%), as demonstrated by the A1:A30 model above. The cost in
the second year is P*(1+5%)^2. The cost in year 30 is P*(1+5%)^29. The
total cost (or cost saving?) over 30 years is P + P*(1+5%) +...+
P*(1+5%)^29.

Look familiar? QED.


----- original message -----
 
The FV formula assumes a consistent payment amount and a compouding rate of
interest. The person buying the solar array is not investing (or spending) a
consistent amount of money. The person buying the array is anticipating
spending 5% more each year. Your assumption is that the 5% is the compounding
rate of interest. If the person buying the array was anticipating saving the
same amount of money each year and investing that at 5% then your method
would be valid but I think that interpretation is a bit of a stretch.

I assumed the classic accounting question of do I buy the machine. If the
net present value of the future cash flows is greater than the present cost
of the asset then you are financially haead to purchase the asset.
 
Jim Thomlinson said:
The FV formula assumes a consistent payment amount and a compouding
rate of interest.
Click to expand...

That's a very narrow understanding of the FV formula. I think you missed
the point of my explanation. Perhaps if you study it again with a more open
mind, it will click. Otherwise, my approach does not further your
understanding. There is no benefit to you by my continuing to try.

If the net present value of the future cash flows is greater than the
present
cost of the asset then you are financially haead to purchase the asset.
Click to expand...

That is certainly one approach. It is not the only approach. And it is not
Kyle's approach, if I understand his question correctly. If I do (and only
Kyle can say yea or nay), your argument is with Kyle, not with me.


----- original message -----
 
Back
Top