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;
}