Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
FV function as an algebraic expression
Can anyone tell me the underlying equation used by the FV function expressed as an algebraic equation? I am creating a flash tool that will do the calculation online, but need to know what the equation is so that it can be programmed in. The FV function uses the following syntax and arguments FV(rate,nper,pmt,pv,type) whe Rate is the interest rate per period. Nper is the total number of payment periods in an annuity. Pmt is the payment made each period; it cannot change over the life of the annuity. If pmt is omitted, you must include the pv argument. Pv is the present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument. Type is the number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
FV function as an algebraic expression
gavin, It is simply exponential growth: FV = P(1+r)^periods where r is the rate of return, expressed as a decimal (5% = 0.05) Note that return and period basis need to be consistent - 5% per year, then periods is in # of years. You can always do the period basis internally FV = P(1+r/n)^(Y*n) where n is the number of sub periods per time basis - usually, 12 months per year..... HTH, Bernie MS Excel MVP "gavin" wrote in message ... Can anyone tell me the underlying equation used by the FV function expressed as an algebraic equation? I am creating a flash tool that will do the calculation online, but need to know what the equation is so that it can be programmed in. The FV function uses the following syntax and arguments FV(rate,nper,pmt,pv,type) whe Rate is the interest rate per period. Nper is the total number of payment periods in an annuity. Pmt is the payment made each period; it cannot change over the life of the annuity. If pmt is omitted, you must include the pv argument. Pv is the present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument. Type is the number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
FV function as an algebraic expression
Hi Bert Thanks for your help on this. The formula works for making monthly payments P over a set number of periods. It doesn't seem to allow for an initial lump sum payment at the beginning of the period. The FV dialoge box in excel allows for the input of a Pv value. I believe the initial lump sum , of say £250, would go in here. If you know how Excel inputs the initial lump sum into the equation would be a great help. Cheers Gavin "Bernie Deitrick" wrote: gavin, It is simply exponential growth: FV = P(1+r)^periods where r is the rate of return, expressed as a decimal (5% = 0.05) Note that return and period basis need to be consistent - 5% per year, then periods is in # of years. You can always do the period basis internally FV = P(1+r/n)^(Y*n) where n is the number of sub periods per time basis - usually, 12 months per year..... HTH, Bernie MS Excel MVP "gavin" wrote in message ... Can anyone tell me the underlying equation used by the FV function expressed as an algebraic equation? I am creating a flash tool that will do the calculation online, but need to know what the equation is so that it can be programmed in. The FV function uses the following syntax and arguments FV(rate,nper,pmt,pv,type) whe Rate is the interest rate per period. Nper is the total number of payment periods in an annuity. Pmt is the payment made each period; it cannot change over the life of the annuity. If pmt is omitted, you must include the pv argument. Pv is the present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument. Type is the number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
FV function as an algebraic expression
Gavin,
Actually, the formula that I gave is for an initial value P, without additional payments. If you want to include periodic payments, you need to use a summation, where each payment is basically calculated separately: FV = Sum for i = 0 to NP-1 of Payment for that period * (1+rate per period)^(NP-i) (I cannot include the greek letter captial sigma.....) Payment 0 would be the initial lump sum - all other payments would be the periodic payment. So, for a four year investiment with monthly payments, you would need to loop through and do 48 calculations. HTH, Bernie MS Excel MVP "gavin" wrote in message ... Hi Bert Thanks for your help on this. The formula works for making monthly payments P over a set number of periods. It doesn't seem to allow for an initial lump sum payment at the beginning of the period. The FV dialoge box in excel allows for the input of a Pv value. I believe the initial lump sum , of say £250, would go in here. If you know how Excel inputs the initial lump sum into the equation would be a great help. Cheers Gavin "Bernie Deitrick" wrote: gavin, It is simply exponential growth: FV = P(1+r)^periods where r is the rate of return, expressed as a decimal (5% = 0.05) Note that return and period basis need to be consistent - 5% per year, then periods is in # of years. You can always do the period basis internally FV = P(1+r/n)^(Y*n) where n is the number of sub periods per time basis - usually, 12 months per year..... HTH, Bernie MS Excel MVP "gavin" wrote in message ... Can anyone tell me the underlying equation used by the FV function expressed as an algebraic equation? I am creating a flash tool that will do the calculation online, but need to know what the equation is so that it can be programmed in. The FV function uses the following syntax and arguments FV(rate,nper,pmt,pv,type) whe Rate is the interest rate per period. Nper is the total number of payment periods in an annuity. Pmt is the payment made each period; it cannot change over the life of the annuity. If pmt is omitted, you must include the pv argument. Pv is the present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument. Type is the number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
FV function as an algebraic expression
Hey Bernie Have tested this formula in Excel, and its does not give the same result as when you input into the FV dialogue. They are close, but not quite the same. It must be something in the way Excel does the calculation. Does Microsoft publish the the formulas they have used when they programmed Excel? Cheers Gavin "Bernie Deitrick" wrote: Gavin, Actually, the formula that I gave is for an initial value P, without additional payments. If you want to include periodic payments, you need to use a summation, where each payment is basically calculated separately: FV = Sum for i = 0 to NP-1 of Payment for that period * (1+rate per period)^(NP-i) (I cannot include the greek letter captial sigma.....) Payment 0 would be the initial lump sum - all other payments would be the periodic payment. So, for a four year investiment with monthly payments, you would need to loop through and do 48 calculations. HTH, Bernie MS Excel MVP "gavin" wrote in message ... Hi Bert Thanks for your help on this. The formula works for making monthly payments P over a set number of periods. It doesn't seem to allow for an initial lump sum payment at the beginning of the period. The FV dialoge box in excel allows for the input of a Pv value. I believe the initial lump sum , of say £250, would go in here. If you know how Excel inputs the initial lump sum into the equation would be a great help. Cheers Gavin "Bernie Deitrick" wrote: gavin, It is simply exponential growth: FV = P(1+r)^periods where r is the rate of return, expressed as a decimal (5% = 0.05) Note that return and period basis need to be consistent - 5% per year, then periods is in # of years. You can always do the period basis internally FV = P(1+r/n)^(Y*n) where n is the number of sub periods per time basis - usually, 12 months per year..... HTH, Bernie MS Excel MVP "gavin" wrote in message ... Can anyone tell me the underlying equation used by the FV function expressed as an algebraic equation? I am creating a flash tool that will do the calculation online, but need to know what the equation is so that it can be programmed in. The FV function uses the following syntax and arguments FV(rate,nper,pmt,pv,type) whe Rate is the interest rate per period. Nper is the total number of payment periods in an annuity. Pmt is the payment made each period; it cannot change over the life of the annuity. If pmt is omitted, you must include the pv argument. Pv is the present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument. Type is the number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
FV function as an algebraic expression
Gavin, http://support.microsoft.com/kb/123757 HTH, Bernie MS Excel MVP "gavin" wrote in message ... Hey Bernie Have tested this formula in Excel, and its does not give the same result as when you input into the FV dialogue. They are close, but not quite the same. It must be something in the way Excel does the calculation. Does Microsoft publish the the formulas they have used when they programmed Excel? Cheers Gavin "Bernie Deitrick" wrote: Gavin, Actually, the formula that I gave is for an initial value P, without additional payments. If you want to include periodic payments, you need to use a summation, where each payment is basically calculated separately: FV = Sum for i = 0 to NP-1 of Payment for that period * (1+rate per period)^(NP-i) (I cannot include the greek letter captial sigma.....) Payment 0 would be the initial lump sum - all other payments would be the periodic payment. So, for a four year investiment with monthly payments, you would need to loop through and do 48 calculations. HTH, Bernie MS Excel MVP "gavin" wrote in message ... Hi Bert Thanks for your help on this. The formula works for making monthly payments P over a set number of periods. It doesn't seem to allow for an initial lump sum payment at the beginning of the period. The FV dialoge box in excel allows for the input of a Pv value. I believe the initial lump sum , of say £250, would go in here. If you know how Excel inputs the initial lump sum into the equation would be a great help. Cheers Gavin "Bernie Deitrick" wrote: gavin, It is simply exponential growth: FV = P(1+r)^periods where r is the rate of return, expressed as a decimal (5% = 0.05) Note that return and period basis need to be consistent - 5% per year, then periods is in # of years. You can always do the period basis internally FV = P(1+r/n)^(Y*n) where n is the number of sub periods per time basis - usually, 12 months per year..... HTH, Bernie MS Excel MVP "gavin" wrote in message ... Can anyone tell me the underlying equation used by the FV function expressed as an algebraic equation? I am creating a flash tool that will do the calculation online, but need to know what the equation is so that it can be programmed in. The FV function uses the following syntax and arguments FV(rate,nper,pmt,pv,type) whe Rate is the interest rate per period. Nper is the total number of payment periods in an annuity. Pmt is the payment made each period; it cannot change over the life of the annuity. If pmt is omitted, you must include the pv argument. Pv is the present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument. Type is the number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
FV function as an algebraic expression
Thanks Bernie This is very helpful. Cheers Gavin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Algebraic Function | Excel Discussion (Misc queries) | |||
Excel error: Undefined Function 'InStrRev' in Expression | Excel Worksheet Functions | |||
Null To Zero - ODBC Error - Undefined function 'Nz' in Expression | Excel Programming | |||
algebraic function | Excel Worksheet Functions | |||
Registering function WITHOUT it appearing in the expression builder | Excel Programming |