Functions > Finance > Example: Finance Functions
Example: Finance Functions
1. Define an annual interest rate:
Click to copy this expression
2. Define the number of compounding periods per year:
Click to copy this expression
3. Define a periodic interest rate:
Click to copy this expression
4. Define a number of compounding periods:
Click to copy this expression
5. Define the present and future values of a loan:
Click to copy this expression
Click to copy this expression
Payment
1. Use the pmt function to calculate the required monthly payment to pay off a 10,000 loan, at 6%, over a period of 5 years:
Click to copy this expression
Payments toward a loan are entered and displayed as negative numbers.
2. Use the ppmt function to calculate the 36th principal-only payment of the above loan:
Click to copy this expression
Click to copy this expression
3. Use the ipmt function to calculate the 36th interest-only payment of the above loan:
Click to copy this expression
The principal payment plus the interest payment add up to the total payment PMT.
Click to copy this expression
Cumulative Interest, Cumulative Principal, and APR
1. Use the cumint function to calculate the cumulative interest that is paid on the above loan:
Click to copy this expression
2. Use the cumprn function to calculate the cumulative principal paid on the above loan:
Click to copy this expression
3. Use the eff function to calculate the effective Annual Percentage Rate (APR) paid on the above loan:
Click to copy this expression
Interest Rate
1. Use the crate function to calculate the fixed interest rate per period for an investment to yield a future value fv, given a present value pv and nper number of compounding periods.
Click to copy this expression
The returned value is the per period, or monthly, interest rate. This corresponds to an annual interest rate of 18%.
2. Use the nom function to calculate the nominal interest rate that corresponds to the above (APR):
Click to copy this expression
3. Use the rate function to calculate the interest rate per period of a loan over nper number of compounding periods, given a periodic, constant payment pmt and pv present value.
Click to copy this expression
The returned value is the per period, or monthly, interest rate. This corresponds to an annual interest rate of 6%.
Number of Periods
1. Use function cnper to calculate the number of compounding periods for an investment pv to reach fv assuming you earn an annual interest rate of prate:
Click to copy this expression
2. Use function nper to calculate the number of periods for an investment pv to reach fv assuming you make a monthly payment of PMT and earn an annual interest rate of prate:
Click to copy this expression
Future Value
1. Use the fv function to calculate the future value of an investment based on periodic, constant payments of PMT over nper number of compounding periods and using a fixed interest rate prate.
Click to copy this expression
2. Use the fvadj function to calculate the future value of initial principal princ after applying a series of compound interest rates sched.
Click to copy this expression
Click to copy this expression
Click to copy this expression
3. Use the fvc function to calculate the future value of a series of cash flows occurring at regular intervals earning an interest rate prate.
Click to copy this expression
Click to copy this expression
Was this helpful?