diff --git a/kspread/extensions/financial.xml b/kspread/extensions/financial.xml index c05074938a..2b63933d99 100644 --- a/kspread/extensions/financial.xml +++ b/kspread/extensions/financial.xml @@ -1,309 +1,314 @@ Financial FV Float Present value Float Rate Float Periods Float The FV() function returns the future value of an investment, given the yield and the time elapsed. If you have $1000 in a bank account earning 8% interest, after two years you will have FV(1000;0.08;2) or $1166.40. FV(present value;yield;periods) FV(1000;0.08;2) equals 1166.40 PV Float Future value Float Interest rate Float Periods Float The PV() function returns the present value of an investment -- the value today of a sum of money in the future, given the rate of interest or inflation. For example if you need $1166.40 for your new computer and you want to buy it in two years while earning 8% interest, you need to start with PV(1166.4;0.08;2) or $1000. PV(future value;rate;periods) PV(1166.4;0.08;2) equals 1000 PV_annuity Float Payment per period Float Interest rate Float Periods Float The PV_annuity() function returns the present value of an annuity or stream of payments. For example: a "million dollar" lottery ticket that pays $50,000 a year for 20 years, with an interest rate of 5%, is actually worth PV_annuity(50000;0.05;20) or $623,111. This function assumes that payments are made at the end of each period. PV_annuity(amount;interest;periods) PV_annuity(1000;0.05;5) equals 4329.48 FV_annuity Float Payment per period Float Interest rate Float Periods Float The FV_annuity() function returns the future value of a stream of payments given the amount of the payment, the interest rate and the number of periods. For example: If you receive $500 per year for 20 years, and invest it at 8%, the total after 20 years will be FV_annuity(500;0.08;20) or $22,880.98. This function assumes that payments are made at the end of each period. FV_annuity(amount;interest;periods) FV_annuity(1000;0.05;5) equals 5525.63 compound Float Principal Float Interest rate Float Periods per year Float Years Float The compound() function returns the value of an investment, given the principal, nominal interest rate, compounding frequency and time. For example: $5000 at 12% interest compounded quarterly for 5 years will become compound(5000;0.12;4;5) or $9030.56. compound(initial;interest;periods;periods_per_year) compound(5000;0.12;4;5) equals 9030.56 continuous Float Principal Float Interest rate Float Years Float The continuous() function calculates the return on continuously compounded interest, given the principal, nominal rate and time in years. For example: $1000 earning 10% for 1 year becomes continuous(1000;.1;1) or $1105.17. continuous(principal;interest;years) continuous(1000;0.1;1) equals 1105.17 effective Float Nominal interest rate Float Periods Float The effective() function calculates the effective yield for a nominal interest rate (annual rate or APR). For example: 8% interest compounded monthly provides an effective yield of effective(.08;12) or 8.3%. effective(nominal;periods) effective(0.08;12) equals 0.083 nominal Float Effective interest rate Float Periods Float The nominal() function calculates the nominal (stated) interest rate for an effective (annualized) interest rate compounded at given intervals. For example: to earn 8% on an account compounded monthly, you need a return of nominal(.08;12) or 7.72%. nominal(effective;periods) nominal(0.08;12) equals 0.0772 zero_coupon Float Face value Float Interest rate Float Years Float The zero_coupon() function calculates the value of a zero-coupon (pure discount) bond. For example: if the interest rate is 10%, a $1000 bond that matures in 20 years is worth zero_coupon(1000;.1;20) or $148.64. zero_coupon(face value;rate;years) zero_coupon(1000;.1;20) equals 148.64 level_coupon Float Face value Float Coupon rate Float Coupons per Year Float Years Float Market Interest Rate Float The level_coupon() function calculates the value of a level-coupon bond. For example: if the interest rate is 10%, a $1000 bond with semi-annual coupons at a rate of 13% that matures in 4 years is worth level_coupon(1000;.13;2;4;.1) or $1096.95. zero_coupon(face value;rate;years) level_coupon(1000;.13;2;4;.1) equals 1096.95 SLN Float Cost Float Salvage Float Life Float The SLN() function will determine the straight line depreciation of an asset for a single period. The amount you paid for the asset is the cost, the value of the asset at the end of the period is salvage, and life is the number of periods over which the asset is depreciated. SLN divides the cost evenly over the life of an asset. SLN(cost; salvage value; life) SLN(10000;700;10) equals 930 SYD Float Cost Float Salvage Float Life Float Period Float The SYD() function will calculate the sum-of-years digits depreciation for an asset based on its cost, salvage value, anticipated life, and a particular period. This method accelerates the rate of the depreciation, so that more depreciation expense occurs in earlier periods than in later ones. The depreciable cost is the actual cost minus the salvage value. The useful life is the number of periods (typically years) over with the asset is depreciated. SYD(cost; salvage value; life; period) SYD(5000; 200; 5; 2) equals 1280 DB Float Cost Float Salvage Float Life Float Period Float + + Month + Float + - The DB() function will calculate the depreciation of an asset for a given period using the fixed-declining balance method. - DB(cost; salvage value; life; period) + The DB() function will calculate the depreciation of an asset for a given period using the fixed-declining balance method. Month is optional, if omitted it is assumed to be 12. + DB(cost; salvage value; life; period [;month]) DB(8000;400;6;3) equals 1158.40 + DB(8000;400;6;3;2) equals 1783.41 diff --git a/kspread/kspread_functions_financial.cc b/kspread/kspread_functions_financial.cc index 0b2980bac7..2b1d05ed0e 100644 --- a/kspread/kspread_functions_financial.cc +++ b/kspread/kspread_functions_financial.cc @@ -1,395 +1,405 @@ // built-in financial functions #include "kspread_util.h" #include "kspread_doc.h" #include "kspread_table.h" #include #include #include #include #include #include #include // Function: FV /* Returns future value, given current value, interest rate and time */ bool kspreadfunc_fv( KSContext& context ) { QValueList& args = context.value()->listValue(); if ( !KSUtil::checkArgumentsCount( context, 3, "FV", true ) ) return false; if ( !KSUtil::checkType( context, args[0], KSValue::DoubleType, true ) ) return false; if ( !KSUtil::checkType( context, args[1], KSValue::DoubleType, true ) ) return false; if ( !KSUtil::checkType( context, args[2], KSValue::DoubleType, true ) ) return false; double present = args[0]->doubleValue(); double interest = args[1]->doubleValue(); double periods = args[2]->doubleValue(); context.setValue( new KSValue( present * pow(1+interest, periods))); return true; } // Function: compound /* Returns value after compounded interest, given principal, rate, periods per year and year */ bool kspreadfunc_compound( KSContext& context ) { QValueList& args = context.value()->listValue(); if ( !KSUtil::checkArgumentsCount( context, 4, "compound", true ) ) return false; if ( !KSUtil::checkType( context, args[0], KSValue::DoubleType, true ) ) return false; if ( !KSUtil::checkType( context, args[1], KSValue::DoubleType, true ) ) return false; if ( !KSUtil::checkType( context, args[2], KSValue::DoubleType, true ) ) return false; if ( !KSUtil::checkType( context, args[3], KSValue::DoubleType, true ) ) return false; double principal = args[0]->doubleValue(); double interest = args[1]->doubleValue(); double periods = args[2]->doubleValue(); double years = args[3]->doubleValue(); context.setValue( new KSValue( principal * pow(1+(interest/periods), periods*years))); return true; } // Function: continuous /* Returns value after continuous compounding of interest, given prinicpal, rate and years */ bool kspreadfunc_continuous( KSContext& context ) { // If you still don't understand this, let me know! ;-) jsinger@leeta.net QValueList& args = context.value()->listValue(); if ( !KSUtil::checkArgumentsCount( context, 3, "continuous", true ) ) return false; if ( !KSUtil::checkType( context, args[0], KSValue::DoubleType, true ) ) return false; if ( !KSUtil::checkType( context, args[1], KSValue::DoubleType, true ) ) return false; if ( !KSUtil::checkType( context, args[2], KSValue::DoubleType, true ) ) return false; double principal = args[0]->doubleValue(); double interest = args[1]->doubleValue(); double years = args[2]->doubleValue(); context.setValue( new KSValue( principal * exp(interest * years))); return true; } // Function: PV bool kspreadfunc_pv( KSContext& context ) { /* Returns presnt value, given future value, interest rate and years */ QValueList& args = context.value()->listValue(); if ( !KSUtil::checkArgumentsCount( context, 3, "PV", true ) ) return false; if ( !KSUtil::checkType( context, args[0], KSValue::DoubleType, true ) ) return false; if ( !KSUtil::checkType( context, args[1], KSValue::DoubleType, true ) ) return false; if ( !KSUtil::checkType( context, args[2], KSValue::DoubleType, true ) ) return false; double future = args[0]->doubleValue(); double interest = args[1]->doubleValue(); double periods = args[2]->doubleValue(); context.setValue( new KSValue( future / pow(1+interest, periods))); return true; } // Function: PV_annuity bool kspreadfunc_pv_annuity( KSContext& context ) { /* Returns present value of an annuity or cash flow, given payment, interest rate, periods, initial amount and whether payments are made at the start (TRUE) or end of a period */ QValueList& args = context.value()->listValue(); if ( !KSUtil::checkArgumentsCount( context, 3, "PV_annuity", true ) ) return false; if ( !KSUtil::checkType( context, args[0], KSValue::DoubleType, true ) ) return false; if ( !KSUtil::checkType( context, args[1], KSValue::DoubleType, true ) ) return false; if ( !KSUtil::checkType( context, args[2], KSValue::DoubleType, true ) ) return false; double amount = args[0]->doubleValue(); double interest = args[1]->doubleValue(); double periods = args[2]->doubleValue(); double result; result = amount * (1 - 1/(pow( (1+interest), periods ))) / interest ; context.setValue( new KSValue( result ) ); return true; } // Function: FV_annnuity bool kspreadfunc_fv_annuity( KSContext& context ) { /* Returns future value of an annuity or cash flow, given payment, interest rate and periods */ QValueList& args = context.value()->listValue(); if ( !KSUtil::checkArgumentsCount( context, 3, "FV_annuity", true ) ) return false; if ( !KSUtil::checkType( context, args[0], KSValue::DoubleType, true ) ) return false; if ( !KSUtil::checkType( context, args[1], KSValue::DoubleType, true ) ) return false; if ( !KSUtil::checkType( context, args[2], KSValue::DoubleType, true ) ) return false; double amount= args[0]->doubleValue(); double interest = args[1]->doubleValue(); double periods = args[2]->doubleValue(); double result; result = amount * ((pow( (1+interest),periods))/interest - 1/interest) ; context.setValue( new KSValue( result ) ); return true; } // Function: effective bool kspreadfunc_effective( KSContext& context ) { /* Returns effective interest rate given nominal rate and periods per year */ QValueList& args = context.value()->listValue(); if ( !KSUtil::checkArgumentsCount( context, 2, "effective", true ) ) return false; if ( !KSUtil::checkType( context, args[0], KSValue::DoubleType, true ) ) return false; if ( !KSUtil::checkType( context, args[1], KSValue::DoubleType, true ) ) return false; double nominal = args[0]->doubleValue(); double periods = args[1]->doubleValue(); context.setValue( new KSValue( pow( 1 + (nominal/periods), periods )- 1 ) ); return true; } // Function: zero_coupon bool kspreadfunc_zero_coupon( KSContext& context ) { /* Returns effective interest rate given nominal rate and periods per year */ QValueList& args = context.value()->listValue(); if ( !KSUtil::checkArgumentsCount( context, 3, "zero_coupon", true ) ) return false; if ( !KSUtil::checkType( context, args[0], KSValue::DoubleType, true ) ) return false; if ( !KSUtil::checkType( context, args[1], KSValue::DoubleType, true ) ) return false; if ( !KSUtil::checkType( context, args[2], KSValue::DoubleType, true ) ) return false; double face = args[0]->doubleValue(); double rate = args[1]->doubleValue(); double years = args[2]->doubleValue(); context.setValue( new KSValue( face / pow( (1 + rate), years ) ) ); return true; } // Function: level_coupon bool kspreadfunc_level_coupon( KSContext& context ) { /* Returns effective interest rate given nominal rate and periods per year */ QValueList& args = context.value()->listValue(); if ( !KSUtil::checkArgumentsCount( context, 5, "level_coupon", true ) ) return false; if ( !KSUtil::checkType( context, args[0], KSValue::DoubleType, true ) ) return false; if ( !KSUtil::checkType( context, args[1], KSValue::DoubleType, true ) ) return false; if ( !KSUtil::checkType( context, args[2], KSValue::DoubleType, true ) ) return false; if ( !KSUtil::checkType( context, args[3], KSValue::DoubleType, true ) ) return false; if ( !KSUtil::checkType( context, args[4], KSValue::DoubleType, true ) ) return false; double face = args[0]->doubleValue(); double coupon_rate = args[1]->doubleValue(); double coupon_year = args[2]->doubleValue(); double years = args[3]->doubleValue(); double market_rate = args[4]->doubleValue(); double coupon = coupon_rate * face / coupon_year; double interest = market_rate/coupon_year; double pv_annuity = (1 - 1/(pow( (1+interest), (years*coupon_year) ))) / interest ; context.setValue( new KSValue( coupon * pv_annuity + (face/ pow( (1+interest), (years*coupon_year) ) ) ) ); return true; } // Function: nominal bool kspreadfunc_nominal( KSContext& context ) { /* Returns nominal interest rate given effective rate and periods per year */ QValueList& args = context.value()->listValue(); if ( !KSUtil::checkArgumentsCount( context, 2, "nominal", true ) ) return false; if ( !KSUtil::checkType( context, args[0], KSValue::DoubleType, true ) ) return false; if ( !KSUtil::checkType( context, args[1], KSValue::DoubleType, true ) ) return false; double effective = args[0]->doubleValue(); double periods = args[1]->doubleValue(); if ( periods == 0.0 ) // Check null return false; context.setValue( new KSValue( periods * (pow( (effective + 1), (1 / periods) ) -1) ) ); return true; } // Function: SLN /* straight-line depreciation for a single period */ bool kspreadfunc_sln( KSContext& context ) { QValueList& args = context.value()->listValue(); if ( !KSUtil::checkArgumentsCount( context, 3, "SLN", true ) ) return false; if ( !KSUtil::checkType( context, args[0], KSValue::DoubleType, true ) ) return false; if ( !KSUtil::checkType( context, args[1], KSValue::DoubleType, true ) ) return false; if ( !KSUtil::checkType( context, args[2], KSValue::DoubleType, true ) ) return false; double cost = args[0]->doubleValue(); double salvage_value = args[1]->doubleValue(); double life = args[2]->doubleValue(); // sentinel check if( life <= 0.0 ) return false; context.setValue( new KSValue( (cost - salvage_value) / life ) ); return true; } // Function: SYD /* sum-of-years digits depreciation */ bool kspreadfunc_syd( KSContext& context ) { QValueList& args = context.value()->listValue(); if ( !KSUtil::checkArgumentsCount( context, 4, "SYD", true ) ) return false; if ( !KSUtil::checkType( context, args[0], KSValue::DoubleType, true ) ) return false; if ( !KSUtil::checkType( context, args[1], KSValue::DoubleType, true ) ) return false; if ( !KSUtil::checkType( context, args[2], KSValue::DoubleType, true ) ) return false; if ( !KSUtil::checkType( context, args[3], KSValue::DoubleType, true ) ) return false; double cost = args[0]->doubleValue(); double salvage_value = args[1]->doubleValue(); double life = args[2]->doubleValue(); double period = args[3]->doubleValue(); // sentinel check if( life <= 0.0 ) return false; context.setValue( new KSValue( ( ( (cost - salvage_value) * (life - period + 1) * 2) / (life * (life + 1.0) ) ) ) ) ; return true; } // Function: DB /* fixed-declining depreciation */ bool kspreadfunc_db( KSContext& context ) { QValueList& args = context.value()->listValue(); - if ( !KSUtil::checkArgumentsCount( context, 4, "DB", true ) ) - return false; + double month = 12; + + if( KSUtil::checkArgumentsCount( context, 5, "DB", false ) ) + { + if( !KSUtil::checkType( context, args[4], KSValue::DoubleType, true ) ) + return false; + month = args[4]->doubleValue(); + } + else + { + if ( !KSUtil::checkArgumentsCount( context, 4, "DB", true ) ) + return false; + } if ( !KSUtil::checkType( context, args[0], KSValue::DoubleType, true ) ) return false; if ( !KSUtil::checkType( context, args[1], KSValue::DoubleType, true ) ) return false; if ( !KSUtil::checkType( context, args[2], KSValue::DoubleType, true ) ) return false; if ( !KSUtil::checkType( context, args[3], KSValue::DoubleType, true ) ) return false; double cost = args[0]->doubleValue(); double salvage = args[1]->doubleValue(); double life = args[2]->doubleValue(); double period = args[3]->doubleValue(); - double month = 12; // sentinel check if( cost == 0 || life <= 0.0 ) return false; if( salvage / cost < 0 ) return false; double rate = 1000 * (1 - pow( (salvage/cost), (1/life) )); rate = floor( rate + 0.5 ) / 1000; double total = cost * rate * month / 12; if( period == 1 ) { context.setValue( new KSValue( total ) ); return true; } for( int i = 1; i < life; ++i ) if( i == period-1 ) { context.setValue( new KSValue( rate * (cost-total) ) ); return true; } else total += rate * (cost-total); context.setValue( new KSValue( (cost-total) * rate * (12-month)/12 ) ); return true; }