Week 7 Flashcards
Effective Interest Rate
-Calculated from APR
-What the APR rate looks like based on number of Payments you are making
APR Rate
-Annual percentage rate for the loan
-Shows the interest rate for a whole year rather than monthly payments/rate
ERate formula:
(1+APR Rate/Comp Periods)^(Comp Periods/Payment Periods)-1
Canadian Mortgage Quirk:
Semi annually compounded!!! twice in a year
but still payed monthly (USUALLY) but can be bi-weekly(26) or semi monthly (24)
Canadian Mortgage Erate: IF PAYED MONTHLY
(1+APR Rate/2)^(2/12)-1
This is just a version of:
(1+APR Rate/Comp Periods)^(Comp Periods/Payment Periods)-1
Loan Payments are normally paid
Monthly
Loan Payments E-rate (2)
(1+APR Rate/Comp Periods)^(Comp Periods/Payment Periods)-1
so…
(1+APR Rate/12)^(12/12)-1
APR Rate/12
Credit Card Monthly Payments compounded?
DAILY
Credit Card Erate formula
(1+APR Rate/Comp Periods)^(Comp Periods/Payment Periods)-1
so…
(1+APR Rate/365)^(365/12)-1
How many compounding periods are there for each?
Canadian Mortgage
Credit Card
Loan
Semi Annually (2 times in a year)
Daily (365)
Loan (12)
Daily
Monthly
Quarterly
Semi Annually
Semi Monthly
Annually
bi-weekly
365
12
4
2
24
1
26
When do you use APR rate in the formula?
When calculating Erate for Canadian Mortgages, Loans, Credit Cards
What is the number of payments in the calculating Erate functions?
Always 12!
IMPORTANT FOR PMT FUNCTION!
Do NOT make anything NEGATIVE!!!! BECAUSE payment is money going out of account so pmt has to be negative itself!!
Ammortization Schedlue
Shows what monthly payments look like broken down by payments that go towards the principle and payments that pay down interest
Ammortization:
Principle Pymt Function
=PPMT(Erate,period we are in, #periods,pv)
-Use the Erate (ABS REF)
-Per is the period we are currently in (MIXED REF)
-#Periods is Years*Payments (ABS REF)
-PV is the total valye of the loan rn (ABS REF)
Ammortization:
Interest Payment Function
=IPMT(Erate,period we are in, #periods,pv)
-Use the Erate (ABS REF)
-Per is the period we are currently in
-#Periods is Years*Payments (ABS REF)
-PV is the total valye of the loan rn (ABS REF)
copy across and jus change the P to an I
Ammortization:
Total Payment, how to calculate this and to check if it is right?
=PPMT+IPMT
Check if this value is the same as the PMT calculated that you will pay monthly!
Ammortization
Balance
You are checking what balance you have remaining on the loan!!!
=Loan Principle+sum(PMT range)
Loan Principle: ABS REF!
Sum: must use sum function!!!!!
PMT RANGE: first cell will be like ($F$3:F3)
EXAMPLE: $C$4+SUM($F$3:F3)
How to check if you did Ammortization right?
The total value of balance at the final period should be 0.00
As years of loan go on what happens?
Interest payment values decrease
Principle payment values increase
Two way data table sensitivity analysis
What does the payment look like at different terms and different apr rates?
How to do the two way data table analysis
Top left is the pmt
See what vbl is in the rows, (term) put it in the row input cell
See what vbl is in the columns, (apr rate) put it in the column cell
check with the calculated apr and term to see if the table is done correctly
IMPORTANT THING TO NOTE: BIWEEKLY VS SEMI MONTHLY
BIWEEKLY EVERY TWO WEEKS 26 PAYMENTS
SEMIMONTHLY TWICE A MONTH 24
Trade in Value questions
Trade in value is the amount that a car dealer will PAY you to trade in your car
So if you are using this to calculate a loan value
Loan value= Car price- Downpayment-Trade in Value
Total Paid questions?
=Down Payment+ (Total Payment)
NOTE: signs can be - depending on if total payment is -
Total payment= payment per month*number of month
PMT function
=PMT(RATE, NPER, PV OR FV)
rate=erate
nper= number of months*payments per month
BASICALLY THIS IS THE TOTAL AMOUNT OF TIMES YOU MAKE PAYMENTS!
PV OR FV= dont put negative sign in front because
PMT VALUE HAS TO BE NEGATIVE
Total Interest Paid formula:
=(- Value of Payments per month*Number of Months)-Loan Amount
Calculating in total what you paid, subtracting it from the original cost of the loan
Difference between Loan Payment and Payment per month
Loan Payment= one individual payment
Payment per month= Loan payment *Payment per month
LETS SAY if you have to do 2 loan payments per month, your total payment in one month would be Loan payment*2
Nper
of years * #payments per year
