CS101 .pk
MTH 302
LECTURE 8
Compound Interest
Calculate returns from investments
Annuities
Excel Functions
OBJECTIVES
The objectives of the lecture are to learn about:
Review of lecture 7
Compound Interest
Calculate returns from investments
Annuities
Excel Functions
CUMIPMT
Returns the cumulative interest paid on a loan between start_period and end_period.
If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.
The syntax is as follows:
CUMIPMT(rate,nper,pv,start_period,end_period,type)
Rate: interest rate.
Nper: total number of payment periods
Pv: present value.
Start_period: first period in the calculation
End_period: last period in the calculation
Type: timing of the payment
|Type |Timing |
|0 (zero) |Payment at the end of the period |
|1 |Payment at the beginning of the period |
CUMIPMT-EXAMPLE
Following is an example of CUMIPMT function. In this example, in the first case the objective is to find total interest paid in the second year of payments for periods 13 to 24. Please note there are 12 periods per year. The second case is for the first payment period.
In the first formula, the Annual interest rate 9% is cell A2 (not shown here). The Years of the loan are given in cell A3. The Present value is in cell A4. For the Start period the value 13 was entered. For the End period, the value 24 has been specified. The value of Type is 0, which means that the payment will be at the end of the period. Please note that the annual interest is first divided by 12 to arrive at monthly interest. Then the Years of the loan are multiplied by 12 to get total number of months in the Term of the loan. The answer is (-11135.23).
In the second formula, which gives Interest paid in a single payment in the first month 1 was specified as the Start period. For the End period also the value 1 was entered.This is because only 1 period is under study. All other inputs were the same. The answer is (-937.50).
Data Description
9% Annual interest rate
30 Years of the loan
125,000 Present value
=CUMIPMT(A2/12,A3*12,A4,13,24,0)Total interest paid in the second year of payments, periods 13 through 24 (-11135.23)
=CUMIPMT (A2/12,A3*12,A4,1,1,0)Interest paid in a single payment in the first month (-937.50)
CUMPRINC
The CUMPRINC function returns the cumulative principal paid on a loan between two periods.
The syntax is as under:
CUMPRINC(rate,nper,pv,start_period,end_period,type)
Rate: interest rate.
Nper: total number of payment periods.
Pv: present value
Start_period: period in the calculation. Payment
End_period: last period in the calculation
Type: timing of the payment (0 or 1 as above)
CUMPRINC EXAMPLE
Following is an example of CUMPRINC function. In this example, in the first case the objective is to find the total principal paid in the second year of payments, periods 13 through 24. Please note there are 12 periods per year. The second case is for the principal paid in a single payment in the first month.
In the first formula, the Interest rate per annum 9% is in cell A2 (not shown here). The Term in years (30) is given in cell A3. The Present value is in cell A4. For the Start period the value 13 was entered. For the End period, the value 24 has been specified. The value of Type is 0, which means that the payment will be at the end of the period. Please note that the interest is first divided by 12 to arrive at monthly interest. Then the years of loan are multiplied by 12 to get total number of months in the term of the loan. The answer is (-934.1071).
In the second formula, which gives the principal paid in a single payment in the first month 1 was specified as the start period. For the end period also the value 1 was entered.This is because only 1 period is under study. All other inputs were the same. The answer is (-68.27827).
EXAMPLE
Data Description
9.00% Interest rate per annum
30 Term in years
125,000 Present value
=CUMPRINC(A2/12,A3*12,A4,13,24,0)The total principal paid in the second year of payments, periods 13 through 24 (-934.1071)
=CUMPRINC(A2/12,A3*12,A4,1,1,0)The principal paid in a single payment in the first month (-68.27827)
EFFECT
Returns the effective annual interest rate. As you see there are only two inputs, namely, the nominal interest Nominal_rate and the number of compounding periods per year Npery.
EFFECT(nominal_rate,npery)
Nominal_rate: nominal interest rate
Npery: number of compounding periods per year
EFFECT-EXAMPLE
Here Nominal_rate = 5.25% in cell A2. Npery =4 in cell A3. The answer is 0.053543 or 5.3543%. You should round off the value to 2 decimals. 5.35%.
5.25% Nominal interest rate
4 Number of compounding periods
per year
=EFFECT(A2,A3)
Effective interest rate with the terms above (0.053543 or 5.3543 percent)
FV
Returns the future value of an investment. There are 5 inputs, namely, Rate the interest rate, Nper number of periods, Pmt payment per period, Pv present value and Type.
FV(rate,nper,pmt,pv,type)
Rate: interest rate per period
Nper: total number of payment periods
Pmt: payment made each period.
Pv: present value, or the lump-sum amount
Type: number 0 or 1 due
FV-EXAMPLE 1
In the formula, there are 5 inputs, namely, Rate 6% in cell A2 as the interest rate, 10 as Nper number of periods in cell A3, -200 (notice the minus sign) as Pmt payment per period in cell A4, -500 (notice the minus sign) as Pv present value in cell A4 and 1 as Type in cell A6. The answer is (2581.40).
[pic]
FV-EXAMPLE 2
In the formula, there are 3 inputs, namely, Rate 12% in cell A2 as the interest rate, 12 as Nper number of periods in cell A3, -1000 (notice the minus sign) as Pmt payment per period in cell A4. Pv present value and Type are not specified. Both are not required as we are calculating the Future value of the investment. The answer is (12682.50).
[pic]
FV-EXAMPLE 3
In the formula, there are 4 inputs, namely, Rate 11% in cell A2 as the interest rate, 35 as Nper number of periods in cell A3, -2000 (notice the minus sign) as Pmt payment per period in cell A4, 1as Type in cell A5. The value of Pv was omitted by entering a blank for the value (note the double commas”,,”. The answer is (82846.25).
[pic]
FVSCHEDULE
Returns the future value of an initial principal after applying a series of compound interest rates.
FVSCHEDULE(principal,schedule)
Principal: present value
Schedule: an array of interest rates to apply
FVSCHEDULE-EXAMPLE
In this example, the Principal is 1. The compound rates {0.09,0.11,0.1} are given within curly brackets. The answer is (1.33089).
FVSCHEDULE(principal,schedule)
=FVSCHEDULE(1,{0.09,0.11,0.1})
Future value of 1 with compound interest rates of 0.09,0.11,0.1 (1.33089)
IPMT
Returns the interest payment for an investment for a given period.
IPMT(rate,per,nper,pv,fv,type)
Rate: interest rate per period
Per: period to find the interest
Nper: total number of payment periods
Pv: present value, or the lump-sum amount
Fv: future value, or a cash balance
Type: number 0 or 1
ISPMT
Calculates the interest paid during a specific period of an investment
ISPMT(rate,per,nper,pv)
Rate: interest rate
Per: period
Nper: total number of payment periods
Pv: present value. For a loan, pv is the loan amount
NOMINAL
Returns the annual nominal interest rate.
NOMINAL(effect_rate,npery)
Effect_rate: effective interest rate
Npery: number of compounding periods per year
NPER
Returns the number of periods for an investment.
NPER(rate, pmt, pv, fv, type)
Rate: the interest rate per period.
Pmt: payment made each period
Pv: present value, or the lump-sum amount
Fv: future value, or a cash balance
Type: number 0 or 1 (due)
NPV
Returns the net present value of an investment based on a series of periodic cash flows and a discount rate.
PV(rate,nper,pmt,fv,type)
Rate: interest rate per period
Nper: is the total number of payment periods
Pmt: payment made each period
Fv: future value, or a cash balance Type number 0 or 1 (due)
PMT
Returns the periodic payment for an annuity.
PMT(rate,nper,pv,fv,type)
Rate: interest rate
Nper: total number of payments
Pv: present value
Fv: future value
Type: number 0 (zero) or 1
PPMT
Returns the payment on the principal for an investment for a given period.
PPMT(rate,per,nper,pv,fv,type)
Rate: interest rate per period.
Per: period and must be in the range 1 to nper
Nper: total number of payment periods
Pv: the present value
Fv: future value (0)
Type: the number 0 or 1 (due)
PV
Returns the present value of an investment.
PV(rate,nper,pmt,fv,type)
Rate: interest rate per period
Nper: total number of payment periods in an annuity
Pmt: payment made each period and cannot change over the life of the annuity
Fv: future value, or a cash balance
Type: number 0 or 1 and indicates when payments are due
RATE
Returns the interest rate per period of an annuity.
RATE(nper,pmt,pv,fv,type,guess)
Nper: total number of payment periods
Pmt: payment made each period
Pv: present value
Fv: future value, or a cash balance (0)
Type: number 0 or 1 (due)
Guess: (10%)
RATE-EXAMPLE
Three inputs are specified. 4 as years of loan in cell A5, -200 as monthly payment in cell A6 and 8000 as amount of loan in cell A7. The answer is 0.09241767 or 9.24%.
[pic]
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.