Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compound interest with premium growing
Hi
I would like to know if there is a formula/function to calculate the following: Start premium per month - eg $150 Premium increase per year - eg 5% Number of years - eg 20years Interest rate per year - eg 8% I would like to have the end result, or final total amount and the formula or function to calculate it, thanx. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compound interest with premium growing
Ok, I have the following formula whe
i = interest rate A = payment at beginning of period n = number of payment periods g = growth rate (((1+i)^n-(1+g)^n)/(i-g))*A Now this gives me the answer when I have only one payment per year(A) over a number of years(n). It does not work if I want 12 payments per year, and the growth ia anuually. If I change the number of payments to the number of months, this formula will grow the premium each month by 5% which is not what I want. Any help with this please! "Simba" wrote: Hi I would like to know if there is a formula/function to calculate the following: Start premium per month - eg $150 Premium increase per year - eg 5% Number of years - eg 20years Interest rate per year - eg 8% I would like to have the end result, or final total amount and the formula or function to calculate it, thanx. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compound interest with premium growing
Have a look in Help at the formula FV (Future Value) and also read Help on
PV which gives details on the parameters. Here are some sites that may be helpful http://www.eiu.edu/~dmcgrady/bus3710...functions.html http://www.ehow.com/how_2095624_calc...ent-excel.html http://office.microsoft.com/en-us/ex...698231033.aspx http://office.microsoft.com/en-us/ex...117451033.aspx best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Simba" wrote in message ... Ok, I have the following formula whe i = interest rate A = payment at beginning of period n = number of payment periods g = growth rate (((1+i)^n-(1+g)^n)/(i-g))*A Now this gives me the answer when I have only one payment per year(A) over a number of years(n). It does not work if I want 12 payments per year, and the growth ia anuually. If I change the number of payments to the number of months, this formula will grow the premium each month by 5% which is not what I want. Any help with this please! "Simba" wrote: Hi I would like to know if there is a formula/function to calculate the following: Start premium per month - eg $150 Premium increase per year - eg 5% Number of years - eg 20years Interest rate per year - eg 8% I would like to have the end result, or final total amount and the formula or function to calculate it, thanx. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compound interest with premium growing
"Simba" wrote:
Start premium per month - eg $150 Premium increase per year - eg 5% Number of years - eg 20years Interest rate per year - eg 8% Suppose: A1, initial period premium: 150 A2, annual premium increase: 5% A3, number of years: 20 A4, annual interest rate: 8% A5, number premiums per year: 12 B4, interest rate per period: =A4/A5 (format as Percentage) Then, the future value (about $129,518.70) is: =SUMPRODUCT(FV(B4,A5*(A3-ROW(INDIRECT("1:"&A3))),0, -FV(B4,A5,-ROUND(A1*(1+A2)^(ROW(INDIRECT("1:"&A3))-1),2),0,1))) See "Notes" for an explanation. Notes: 1. If "interest rate" is really the APY (annual percentage yield), B4 should be: =RATE(A5,0,-1,1+A4) It makes a significant difference (about $12,5618.19). Note that if "interest rate" is the APY, the outer FV expression could be simplified. Let me know if you need help with that, if you are interested. The result will be the same either way. 2. The use of ROUND takes into account the real-world constraint that the permium is paid in real currency. Not using ROUND makes a small difference (about $129,518.62). However, note that the appreciated premium is based on the initial premium, not each rounded premium. Using the latter makes a small, but not insignificant difference (about $129,521.73) If you need the latter, I think the only way to compute that is with an annual accumulation schedule. Let me know if you need help with that. 3. You could eliminate __both__ minus signs ("-") before FV and ROUND. That is, either the minus sign must be before both FV and ROUND, or there must not be a minus before both FV and ROUND. My use of the minus signs is a personal preference. 4. Explanation of formula The inner FV expression, -FV(B4,A5,premium,0,1), computes the accumulation each year's premium paid periodically over a year. The outer FV expression, FV(B4,numPeriods,,0,-FV(...)), computes the appreciation of each year's ending balance over the remaining periods. The use of SUMPRODUCT is one way to sum the outer FV experssions. Alternatively, you could use SUM; but that would need to be an array formula. The expression ROW(INDIRECT("1:"&A3)) is a trick to cause SUMPRODUCT (or SUM) to iterate over 1 to A3, the number of years. ----- original message ---- "Simba" wrote: Hi I would like to know if there is a formula/function to calculate the following: Start premium per month - eg $150 Premium increase per year - eg 5% Number of years - eg 20years Interest rate per year - eg 8% I would like to have the end result, or final total amount and the formula or function to calculate it, thanx. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compound interest with premium growing
Potentially confusing typo, albeit unimportant....
I wrote: It makes a significant difference (about $12,5618.19). That should be written $125,618.19. ----- original message ----- "Joe User" wrote: "Simba" wrote: Start premium per month - eg $150 Premium increase per year - eg 5% Number of years - eg 20years Interest rate per year - eg 8% Suppose: A1, initial period premium: 150 A2, annual premium increase: 5% A3, number of years: 20 A4, annual interest rate: 8% A5, number premiums per year: 12 B4, interest rate per period: =A4/A5 (format as Percentage) Then, the future value (about $129,518.70) is: =SUMPRODUCT(FV(B4,A5*(A3-ROW(INDIRECT("1:"&A3))),0, -FV(B4,A5,-ROUND(A1*(1+A2)^(ROW(INDIRECT("1:"&A3))-1),2),0,1))) See "Notes" for an explanation. Notes: 1. If "interest rate" is really the APY (annual percentage yield), B4 should be: =RATE(A5,0,-1,1+A4) It makes a significant difference (about $12,5618.19). Note that if "interest rate" is the APY, the outer FV expression could be simplified. Let me know if you need help with that, if you are interested. The result will be the same either way. 2. The use of ROUND takes into account the real-world constraint that the permium is paid in real currency. Not using ROUND makes a small difference (about $129,518.62). However, note that the appreciated premium is based on the initial premium, not each rounded premium. Using the latter makes a small, but not insignificant difference (about $129,521.73) If you need the latter, I think the only way to compute that is with an annual accumulation schedule. Let me know if you need help with that. 3. You could eliminate __both__ minus signs ("-") before FV and ROUND. That is, either the minus sign must be before both FV and ROUND, or there must not be a minus before both FV and ROUND. My use of the minus signs is a personal preference. 4. Explanation of formula The inner FV expression, -FV(B4,A5,premium,0,1), computes the accumulation each year's premium paid periodically over a year. The outer FV expression, FV(B4,numPeriods,,0,-FV(...)), computes the appreciation of each year's ending balance over the remaining periods. The use of SUMPRODUCT is one way to sum the outer FV experssions. Alternatively, you could use SUM; but that would need to be an array formula. The expression ROW(INDIRECT("1:"&A3)) is a trick to cause SUMPRODUCT (or SUM) to iterate over 1 to A3, the number of years. ----- original message ---- "Simba" wrote: Hi I would like to know if there is a formula/function to calculate the following: Start premium per month - eg $150 Premium increase per year - eg 5% Number of years - eg 20years Interest rate per year - eg 8% I would like to have the end result, or final total amount and the formula or function to calculate it, thanx. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compound interest with premium growing
On 2/17/2010 4:04 AM, Simba wrote:
Hi I would like to know if there is a formula/function to calculate the following: Start premium per month - eg $150 Premium increase per year - eg 5% Number of years - eg 20years Interest rate per year - eg 8% I would like to have the end result, or final total amount and the formula or function to calculate it, thanx. Hi. I think this is correct.... Sub Demo() Debug.Print MyFv(150, 0.05, 0.08, 20) End Sub Function MyFv(n, gr, ir, yr) Dim g, r, r12, k g = 1 + gr r = ir / 12 r12 = 1 + r k = 1 - 1 / r12 ^ 12 MyFv = (n * k * r12 ^ 12 * (r12 ^ (12 * yr) - g ^ yr)) / (r * (r12 ^ 12 - g)) End Function Returns: 128660.8768 = = = = = = = HTH :) Dana DeLouis |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compound interest with premium growing
"Dana DeLouis" wrote:
Hi. I think this is correct.... [....] Returns: 128660.8768 Well, it would be if premiums are paid at the end of each month. But that does not make sense to me. I assume that premiums are paid at the beginning of each month. That is why I use 1 in the last argument of the inner FV expression, -FV(B4,A5,premium,0,1), which computes the accumulation each year's premium paid periodically over a year. You can check your work and experiment with assumptions by using the following paradigm. Suppose: A1, initial period premium: 150 A2, annual premium increase: 5% A3, number of years: 20 A4, annual interest rate: 8% A5, number premiums per year: 12 B4, interest rate per period: =A4/A5 (format as Percentage) Then set: A7, initial premium: =A1 B7, annual accumulation: =FV(B$4,12,-$A7,0,1) A8, subsequent premium: =A7*(1+$A$2) B8, annual accumulation: =FV(B$4,12,-$A8,-B7,1) Copy A8:B8 done through row 26 (20 years). That emulates the computation in your UDF, with the payment-at-beginning change. But as I noted, it would be prudent to round A8 to 2 decimal places due to real-world constraints. However, in that case, perhaps the formula in A8 should be ROUND($A$1*(1+$A$2)^ROW(A1),2). That depends on the OP's requirements. It does not make much difference; but I believe if you're gonna do something, y'might as well do it right. (Note: ROW(A1) is a quick-and-dirty way to generate the exponent 1, 2, 3 etc as the formula is dragged down. Arguably, there are safer ways to do it.) Also as I noted, B4 might be calculated by =RATE(A5,0,-1,1+A4) if A4 is actually the APY, not an annual interest rate. Again, that depends on the OP's requirements. As an aside.... Function MyFv(n, gr, ir, yr) Dim g, r, r12, k It is usually prudent to explicitly type variables. "Dim g as Double" etc is more efficient. "Function MyFv(...) as Double" is arguably better unless you want to return errors with CVErr(). ----- original message ----- "Dana DeLouis" wrote in message ... On 2/17/2010 4:04 AM, Simba wrote: Hi I would like to know if there is a formula/function to calculate the following: Start premium per month - eg $150 Premium increase per year - eg 5% Number of years - eg 20years Interest rate per year - eg 8% I would like to have the end result, or final total amount and the formula or function to calculate it, thanx. Hi. I think this is correct.... Sub Demo() Debug.Print MyFv(150, 0.05, 0.08, 20) End Sub Function MyFv(n, gr, ir, yr) Dim g, r, r12, k g = 1 + gr r = ir / 12 r12 = 1 + r k = 1 - 1 / r12 ^ 12 MyFv = (n * k * r12 ^ 12 * (r12 ^ (12 * yr) - g ^ yr)) / (r * (r12 ^ 12 - g)) End Function Returns: 128660.8768 = = = = = = = HTH :) Dana DeLouis |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compound interest with premium growing
I assume that premiums are paid at the beginning of each month. Then, the future value (about $129,518.70) is: =SUMPRODUCT(FV(B4,A5*(A3-ROW(INDIRECT("1:"&A3))),0, -FV(B4,A5,-ROUND(A1*(1+A2)^(ROW(INDIRECT("1:"&A3))-1),2),0,1))) Ok. Thanks. I switched it to payments at the beginning of each month If interested, here is what I get. Sub Demo() Debug.Print MyFv(150, 0.05, 0.08, 20) End Sub Returns: 129518.616 Function MyFv(n, gr, ir, yr) Dim g As Double Dim r As Double Dim w As Double Dim k As Double g = 1 + gr r = ir / 12 w = 1 + r k = 1 + r - 1 / w ^ 11 MyFv = (n * k * w ^ 12 * (w ^ (12*yr) - g^yr)) / (r * (w^12 - g)) End Function |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compound interest with premium growing
"Dana DeLouis" wrote, in response to excerpts from
difference postings from me: Then, the future value (about $129,518.70) is: =SUMPRODUCT(FV(B4,A5*(A3-ROW(INDIRECT("1:"&A3))),0, -FV(B4,A5,-ROUND(A1*(1+A2)^(ROW(INDIRECT("1:"&A3))-1),2),0,1))) [....] I switched it to payments at the beginning of each month If interested, here is what I get. [....] Returns: 129518.616 The difference -- my 129,518.70 (about) v. your 129,518.62 (about) -- is due to rounding of the monthly payment, increased by 5% each year. As I wrote in the "Notes" of my first response: "2. The use of ROUND takes into account the real-world constraint that the permium is paid in real currency. Not using ROUND makes a small difference (about $129,518.62)." I would be interested in the algebraic derivation of your formula. I think I see one way to do it. But where I'm headed with it does not seem to be as clean as yours. However, I do not have time right now to finish the derivation and see if it "cleans up nicely". ----- original message ----- "Dana DeLouis" wrote in message ... I assume that premiums are paid at the beginning of each month. Then, the future value (about $129,518.70) is: =SUMPRODUCT(FV(B4,A5*(A3-ROW(INDIRECT("1:"&A3))),0, -FV(B4,A5,-ROUND(A1*(1+A2)^(ROW(INDIRECT("1:"&A3))-1),2),0,1))) Ok. Thanks. I switched it to payments at the beginning of each month If interested, here is what I get. Sub Demo() Debug.Print MyFv(150, 0.05, 0.08, 20) End Sub Returns: 129518.616 Function MyFv(n, gr, ir, yr) Dim g As Double Dim r As Double Dim w As Double Dim k As Double g = 1 + gr r = ir / 12 w = 1 + r k = 1 + r - 1 / w ^ 11 MyFv = (n * k * w ^ 12 * (w ^ (12*yr) - g^yr)) / (r * (w^12 - g)) End Function |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compound interest with premium growing
Hi. Forgot to mention earlier...
Just having fun playing a little detective work here... We will probably never hear from the OP, but when he mentioned the following... (((1+i)^n-(1+g)^n)/(i-g))*A Now this gives me the answer when I have only one payment per year(A) over a number of years(n). The equation that he is saying "works" appears to be for payments that are at the end of each period, and not at the beginning. I may be wrong, but just thought I'd mention it. :) Dana DeLouis On 2/18/10 1:10 PM, Joe User wrote: "Dana DeLouis" wrote, in response to excerpts from difference postings from me: Then, the future value (about $129,518.70) is: =SUMPRODUCT(FV(B4,A5*(A3-ROW(INDIRECT("1:"&A3))),0, -FV(B4,A5,-ROUND(A1*(1+A2)^(ROW(INDIRECT("1:"&A3))-1),2),0,1))) [....] I switched it to payments at the beginning of each month If interested, here is what I get. [....] Returns: 129518.616 The difference -- my 129,518.70 (about) v. your 129,518.62 (about) -- is due to rounding of the monthly payment, increased by 5% each year. As I wrote in the "Notes" of my first response: "2. The use of ROUND takes into account the real-world constraint that the permium is paid in real currency. Not using ROUND makes a small difference (about $129,518.62)." I would be interested in the algebraic derivation of your formula. I think I see one way to do it. But where I'm headed with it does not seem to be as clean as yours. However, I do not have time right now to finish the derivation and see if it "cleans up nicely". ----- original message ----- "Dana DeLouis" wrote in message ... I assume that premiums are paid at the beginning of each month. Then, the future value (about $129,518.70) is: =SUMPRODUCT(FV(B4,A5*(A3-ROW(INDIRECT("1:"&A3))),0, -FV(B4,A5,-ROUND(A1*(1+A2)^(ROW(INDIRECT("1:"&A3))-1),2),0,1))) Ok. Thanks. I switched it to payments at the beginning of each month If interested, here is what I get. Sub Demo() Debug.Print MyFv(150, 0.05, 0.08, 20) End Sub Returns: 129518.616 Function MyFv(n, gr, ir, yr) Dim g As Double Dim r As Double Dim w As Double Dim k As Double g = 1 + gr r = ir / 12 w = 1 + r k = 1 + r - 1 / w ^ 11 MyFv = (n * k * w ^ 12 * (w ^ (12*yr) - g^yr)) / (r * (w^12 - g)) End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
compound interest | Excel Discussion (Misc queries) | |||
Compound Interest | Excel Discussion (Misc queries) | |||
Compound interest | Excel Worksheet Functions | |||
Compound Interest | Excel Worksheet Functions | |||
compound interest | Excel Worksheet Functions |