Answer:
Task A:
What is the effecting annual rate changed on this loan?
Answer is 3.03%
Task B:
What would be the quarterly payment on this loan?
Answer is $5,403.06
Task C:
Dr. Zoidberg also discovers that instead of the special promotional rate he can make an additional down payment of $20,000 that would lower his loan amount accordingly (i.e. by $20,000). At what APR would Dr. Zoidberg have the same quarterly payment with this option as with the initial promotional rate of 3%?
Answer is 12.21%
Task D
Dr. Zoidberg finds that he can get 1.5% APR if he elects option (c). What will his quarterly payment be under this option?
The answer is $4,159.37
Task E:
Now assume that that payment frequency changes to annual, preserving the same EAR. What is his payment now?
The answer is $21,835.46
Step-by-step explanation:
Task A:
What is the effecting annual rate changed on this loan?
Solution:
Effective annual rate = (1 + (APR/n))ⁿ - 1
where
n = number of compounding periods per year = 4 (compounding quarterly)
APR = 3%
Effective annual rate = (1 + (3%/4))⁴ - 1
Effective annual rate = 3.03% (answer).
Task B:
What would be the quarterly payment on this loan?
Solution:
Quarterly loan payment is calculated using PMT function in Excel :
Rate = 3% / 4 (converting annual rate into Quarterly rate)
nper = 5*4 (5 year loan with 12 Quarterly payments each year)
pv = 100000 (loan amount)
PMT Formula = PMT(3%/4,5*4,100000)
PMT is calculated to be $5,403.06 (answer)
Note: PMT calculation has been attached.
Task C:
Dr. Zoidberg also discovers that instead of the special promotional rate he can make an additional down payment of $20,000 that would lower his loan amount accordingly (i.e. by $20,000). At what APR would Dr. Zoidberg have the same quarterly payment with this option as with the initial promotional rate of 3%?
Solution
The quarterly rate to have the same quarterly payment is calculated using RATE function in Excel :
nper = 5*4 (5 year loan with 12 Quarterly payments each year)
pmt = -5403.06 (Quarterly payment. This is entered with a negative sign because it is a payment)
pv = 80000 (loan amount)
RATE is calculated to be 3.05%. This is the quarterly rate. To get APR, we multiply by 4.
Formula for APR = RATE(5*4,C1,80000)*4
APR = 12.21% (answer)
Task D
Dr. Zoidberg finds that he can get 1.5% APR if he elects option (c). What will his quarterly payment be under this option?
Solution:
Quarterly loan payment is calculated using PMT function in Excel :
rate = 1.5% / 4 (converting annual rate into Quarterly rate)
nper = 5*4 (5 year loan with 12 Quarterly payments each year)
pv = 80000 (loan amount)
PMT formula: PMT(1.5%/4,5*4,80000)
PMT is calculated to be $4,159.37
Task E
Now assume that that payment frequency changes to annual, preserving the same EAR. What is his payment now?
Solution:
PMT = PMT(3%,5,100000)
PMT = $21,835.46