If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Interest rate question
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. |
#2
|
|||
|
|||
Interest rate question
"Kyle P." wrote:
I need to determine how much money a solar array will save one of my customers over the next thirty years. =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 ----- "Kyle P." wrote: 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. |
#3
|
|||
|
|||
Interest rate question
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 -- HTH... Jim Thomlinson "Kyle P." wrote: 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. |
#4
|
|||
|
|||
Interest rate question
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. -- HTH... Jim Thomlinson "Joe User" wrote: "Kyle P." wrote: I need to determine how much money a solar array will save one of my customers over the next thirty years. =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 ----- "Kyle P." wrote: 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. |
#5
|
|||
|
|||
Interest rate question
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 -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "Kyle P." wrote: 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. |
#6
|
|||
|
|||
Interest rate question
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. Kyle P. wrote: 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. |
#7
|
|||
|
|||
Interest rate question
"Jim Thomlinson" wrote:
Are you sure that is correct. 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. 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 ----- "Jim Thomlinson" wrote in message news 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. -- HTH... Jim Thomlinson "Joe User" wrote: "Kyle P." wrote: I need to determine how much money a solar array will save one of my customers over the next thirty years. =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 ----- "Kyle P." wrote: 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. |
#8
|
|||
|
|||
Interest rate question
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. -- HTH... Jim Thomlinson "Joe User" wrote: "Jim Thomlinson" wrote: Are you sure that is correct. 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. 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 ----- "Jim Thomlinson" wrote in message news 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. -- HTH... Jim Thomlinson "Joe User" wrote: "Kyle P." wrote: I need to determine how much money a solar array will save one of my customers over the next thirty years. =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 ----- "Kyle P." wrote: 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. . |
#9
|
|||
|
|||
Interest rate question
"Jim Thomlinson" wrote:
The FV formula assumes a consistent payment amount and a compouding rate of interest. 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. 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 ----- "Jim Thomlinson" wrote in 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. -- HTH... Jim Thomlinson "Joe User" wrote: "Jim Thomlinson" wrote: Are you sure that is correct. 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. 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 ----- "Jim Thomlinson" wrote in message news 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. -- HTH... Jim Thomlinson "Joe User" wrote: "Kyle P." wrote: I need to determine how much money a solar array will save one of my customers over the next thirty years. =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 ----- "Kyle P." wrote: 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. . |
Thread Tools | |
Display Modes | |
|
|