Answer:
Task A:
<u>What is the effecting annual rate changed on this loan?</u>
Answer is 3.03%
<u>Task B: </u>
<u>What would be the quarterly payment on this loan?</u>
Answer is $5,403.06
<u>Task C:</u>
<u>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%?</u>
Answer is 12.21%
<u>Task D</u>
<u>Dr. Zoidberg finds that he can get 1.5% APR if he elects option (c). What will his quarterly payment be under this option?</u>
The answer is $4,159.37
<u>Task E:</u>
<u>Now assume that that payment frequency changes to annual, preserving the same EAR. What is his payment now?</u>
The answer is $21,835.46
Explanation:
<h2>Task A: </h2><h3>What is the effecting annual rate changed on this loan?</h3>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).
<h2>Task B: </h2><h3>What would be the quarterly payment on this loan?</h3>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.
<h2>Task C:</h2><h3>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%?</h3>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)
<h2>Task D</h2><h3>Dr. Zoidberg finds that he can get 1.5% APR if he elects option (c). What will his quarterly payment be under this option?</h3>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
<h2>Task E</h2><h3>Now assume that that payment frequency changes to annual, preserving the same EAR. What is his payment now?</h3>Solution:
PMT = PMT(3%,5,100000)
PMT = $21,835.46
