It is possible to use a calculator (or even pencil and paper) to do all sorts of calculations with money and interest. However, it is easier to use a spreadsheet application like Microsoft Excel. In this section I fiddle with financial equations, especially the basic financial functions in Excel, for the sake of my own understanding.
Here are some Excel documents I made that are useful for doing some financial calculations. Don't forget that some of the formulas require that you activate the Analysis Tools Pack via Tools > Add-Ins.
Everyone wants their money to be in an asset. You want more money back than what you gave out. In other words: if I borrow $100 now, then I eventually pay back $100 plus interest.
There are many ways to give and return money.
A lump sum is one chunk of money.
An annuity is a contiguous series of payments paid at regular intervals, where the payments are uniform, i.e. equal-sized or constant. EG: $1, $1, $1, $1..
Arithmetic payments are a contiguous series of payments paid at regular intervals, where the difference between one payment and the next is calculated by adding some constant value, G. Usually the mth payment is (m-1)G. EG: $1, $2, $3, $4.
Geometric payments are a contiguous series of payments paid at regular intervals, where the difference between one payment and the next is calculated by multiplying by some constant value, g. Usually the mth payment is the first payment multiplied by (1 + g)^{m-1}. EG: $1, $2, $4, $8.
Variable interest payments are variations of the above, but where the interest rate is not constant.
Combinations payments are any mix of the above.
It is worth noting the difference between the following two terms:
ROI (Return On Investment). The money you net on an investment.
ROR (Rate Of Return). The Return on Investment divided by a length of time.
Here I shall conceptually explain some of the more common arguments for Excel financial functions. Please note that I will show Excel functions IN ALL CAPS, although the case is actually irrelevant for designating functions in Excel; also, I will show required arguments for Excel functions in bold.
pv
Present Value. Aka the principal. A present sum of money.
rate
Interest RATE per pay period. EG: If the annual interest rate is 12% and payments are made monthly, than the rate = 12% / 12 = 1%.
nper
Total Number of pay PERiods. EG: If the loan is paid monthly for 2 and a half years, then the nper = 2.5 * 12 = 30.
pmt
PayMenT per pay period. For annuities this is constant and usually includes interest payments.
fv
Future Value. Aka the cash balance. This is usually the pv with the appropriate amount of interest added to it.
type
TYPE of loan. This is whether the payment are due at the end of the pay period (0, the default value); or the due at the beginning of the pay period (1).
per
m pay PERiod out of nper pay periods. EG: If the loan has 10 pay periods, than you can set per = 1, 2, 3, ..., or 10.
guess
GUESS of what the rate is. Some of the functions use iterative methods to calculate a value. Guess provides a seed to start the iterations. If omitted, guess is assumed to be 10%.
The cool thing is that not only does Excel use an equation that combines the two equations above, but also throws in a fix so it can handle annuity payments that occur either at the end or the beginning of a pay period:
Here is a list of all 53 financial functions in Excel, copied directly from the Help for Excel 2000 (ver. 9). After this list I shall try to group these functions by category.
ACCRINT Returns the accrued interest for a security that pays periodic interest
ACCRINTM Returns the accrued interest for a security that pays interest at maturity
AMORDEGRC Returns the depreciation for each accounting period
AMORLINC Returns the depreciation for each accounting period
COUPDAYBS Returns the number of days from the beginning of the coupon period to the settlement date
COUPDAYS Returns the number of days in the coupon period that contains the settlement date
COUPDAYSNC Returns the number of days from the settlement date to the next coupon date
COUPNCD Returns the next coupon date after the settlement date
COUPNUM Returns the number of coupons payable between the settlement date and maturity date
COUPPCD Returns the previous coupon date before the settlement date
CUMIPMT Returns the cumulative interest paid between two periods
CUMPRINC Returns the cumulative principal paid on a loan between two periods
DB Returns the depreciation of an asset for a specified period using the fixed-declining balance method
DDB Returns the depreciation of an asset for a specified period using the double-declining balance method or some other method you specify
DISC Returns the discount rate for a security
DOLLARDE Converts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number
DOLLARFR Converts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction
DURATION Returns the annual duration of a security with periodic interest payments
EFFECT Returns the effective annual interest rate
FV Returns the future value of an investment
FVSCHEDULE Returns the future value of an initial principal after applying a series of compound interest rates
INTRATE Returns the interest rate for a fully invested security
IPMT Returns the interest payment for an investment for a given period
IRR Returns the internal rate of return for a series of cash flows
ISPMT Calculates the interest paid during a specific period of an investment.
MDURATION Returns the Macauley modified duration for a security with an assumed par value of $100
MIRR Returns the internal rate of return where positive and negative cash flows are financed at different rates
NOMINAL Returns the annual nominal interest rate
NPER Returns the number of periods for an investment
NPV Returns the net present value of an investment based on a series of periodic cash flows and a discount rate
ODDFPRICE Returns the price per $100 face value of a security with an odd first period
ODDFYIELD Returns the yield of a security with an odd first period
ODDLPRICE Returns the price per $100 face value of a security with an odd last period
ODDLYIELD Returns the yield of a security with an odd last period
PMT Returns the periodic payment for an annuity
PPMT Returns the payment on the principal for an investment for a given period
PRICE Returns the price per $100 face value of a security that pays periodic interest
PRICEDISC Returns the price per $100 face value of a discounted security
PRICEMAT Returns the price per $100 face value of a security that pays interest at maturity
PV Returns the present value of an investment
RATE Returns the interest rate per period of an annuity
RECEIVED Returns the amount received at maturity for a fully invested security
SLN Returns the straight-line depreciation of an asset for one period
SYD Returns the sum-of-years' digits depreciation of an asset for a specified period
TBILLEQ Returns the bond-equivalent yield for a Treasury bill
TBILLPRICE Returns the price per $100 face value for a Treasury bill
TBILLYIELD Returns the yield for a Treasury bill
VDB Returns the depreciation of an asset for a specified or partial period using a declining balance method
XIRR Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic
XNPV Returns the net present value for a schedule of cash flows that is not necessarily periodic
YIELD Returns the yield on a security that pays periodic interest
YIELDDISC Returns the annual yield for a discounted security. For example, a Treasury bill
YIELDMAT Returns the annual yield of a security that pays interest at maturity
Basic annuity functions
CUMIPMT Returns the cumulative interest paid between two periods
CUMPRINC Returns the cumulative principal paid on a loan between two periods
FV Returns the future value of an investment
IPMT Returns the interest payment for an investment for a given period
NPER Returns the number of periods for an investment
PMT Returns the periodic payment for an annuity
PPMT Returns the payment on the principal for an investment for a given period
PV Returns the present value of an investment
RATE Returns the interest rate per period of an annuity
Just above basic financial functions
FVSCHEDULE Returns the future value of an initial principal after applying a series of compound interest rates
IRR Returns the internal rate of return for a series of cash flows
NPV Returns the net present value of an investment based on a series of periodic cash flows and a discount rate
MIRR Returns the internal rate of return where positive and negative cash flows are financed at different rates
XIRR Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic
XNPV Returns the net present value for a schedule of cash flows that is not necessarily periodic
Coupon functions
COUPDAYBS Returns the number of days from the beginning of the coupon period to the settlement date
COUPDAYS Returns the number of days in the coupon period that contains the settlement date
COUPDAYSNC Returns the number of days from the settlement date to the next coupon date
COUPNCD Returns the next coupon date after the settlement date
COUPNUM Returns the number of coupons payable between the settlement date and maturity date
COUPPCD Returns the previous coupon date before the settlement date
Dollar conversions between decimal and fractions
DOLLARDE Converts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number
DOLLARFR Converts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction
Treasury bill functions
TBILLEQ Returns the bond-equivalent yield for a Treasury bill
TBILLPRICE Returns the price per $100 face value for a Treasury bill
TBILLYIELD Returns the yield for a Treasury bill
Effective & nominal interest rates
EFFECT Returns the effective annual interest rate
NOMINAL Returns the annual nominal interest rate
Security functions.
ACCRINT Returns the accrued interest for a security that pays periodic interest
ACCRINTM Returns the accrued interest for a security that pays interest at maturity
DISC Returns the discount rate for a security
DURATION Returns the annual duration of a security with periodic interest payments
INTRATE Returns the interest rate for a fully invested security
ISPMT Calculates the interest paid during a specific period of an investment.
MDURATION Returns the Macauley modified duration for a security with an assumed par value of $100
ODDFPRICE Returns the price per $100 face value of a security with an odd first period
ODDFYIELD Returns the yield of a security with an odd first period
ODDLPRICE Returns the price per $100 face value of a security with an odd last period
ODDLYIELD Returns the yield of a security with an odd last period
PRICE Returns the price per $100 face value of a security that pays periodic interest
PRICEDISC Returns the price per $100 face value of a discounted security
PRICEMAT Returns the price per $100 face value of a security that pays interest at maturity
RECEIVED Returns the amount received at maturity for a fully invested security
YIELD Returns the yield on a security that pays periodic interest
YIELDDISC Returns the annual yield for a discounted security. For example, a Treasury bill
YIELDMAT Returns the annual yield of a security that pays interest at maturity
Depreciation functions.
AMORDEGRC Returns the depreciation for each accounting period
AMORLINC Returns the depreciation for each accounting period
DB Returns the depreciation of an asset for a specified period using the fixed-declining balance method
DDB Returns the depreciation of an asset for a specified period using the double-declining balance method or some other method you specify
SLN Returns the straight-line depreciation of an asset for one period
SYD Returns the sum-of-years' digits depreciation of an asset for a specified period
VDB Returns the depreciation of an asset for a specified or partial period using a declining balance method