S&s Air's Mortgage
Krystina Lawrence
BA 316-01
Case #2
S&s Air’s Mortgage
- The monthly payments for the 30 year mortgage are $212,098.17. I got this by using the PMT formula in excel. The formula was: =-PMT(0.061/12,30*12,35000000,0,0). For the 20 year mortgage, the monthly payments are $252,774.22. I got this by using the same formula, except substituting the 20 for the 30 for the number of periods.
pmt # | Beg Bal | pmt | int pd | princ pd | end bal |
1 | $ 35,000,000.00 | $212,098.17 | $ 177,916.67 | $34,181.51 | $34,965,818.49 |
2 | $34,965,818.49 | $212,098.17 | $177,742.91 | $34,355.26 | $34,931,463.23 |
3 | $34,931,463.23 | $212,098.17 | $177,568.27 | $34,529.90 | $34,896,933.32 |
4 | $34,896,933.32 | $212,098.17 | $177,392.74 | $34,705.43 | $34,862,227.89 |
5 | $34,862,227.89 | $212,098.17 | $177,216.33 | $34,881.85 | $34,827,346.04 |
6 | $34,827,346.04 | $212,098.17 | $177,039.01 | $35,059.17 | $34,792,286.88 |
$34,181.51 is the amount of the first payment that goes toward the principal.
- It will take them about 24.4 years to pay off the smart loan. I got this answer by using the =NPER equation in excel. The equation was, =NPER(.061/26,-106049.09,35000000,0,0). The answer I got was 635. I then divided this number by 26 to get the number of years. This will take less time because they are technically making 26 half payments per year, which is equivalent to 13 monthly payments. That one month “extra” payment applies to the principal and the loan will be paid off quicker. The interest saved is simply the difference in the total payments. 360 months x $212,098.17 = $76,355,341.20
635 bi-weekly x $106,049.09 = $67,341,172.15
They will save $9,014,169.05 in interest.
- For the first 60 payments, they will pay the same as the 30 year mortgage, $212,098.17. Immediately following that, they will pay the present value of the remaining principal. The remaining balance that is due is $32,609,015.35. I got the number by using the PV formula in excel: =PV(.061/12,300,-212098.17,0,0). Under the bullet loan, they will owe the 60th month payment of $212,098.17, and the remainder of the principal, $32,609,015.35, which will be a total of $32,821,113.52.
- With the interest only loan, they will pay interest for the first 10 years (120 months) with a rate of 3.5%. $35,000,000 x .035/12 = $102,083.33. They then must pay the final payment of the interest and the principal. $35,000,000 + $102,083.33 = $35,102,083.33.
- I think the 20 year loan would be the best option for S&S. Ultimately by using this loan, they would be saving just about $26 million dollars. The main thing that would decide if this is do-able, is if they have enough cash flows to handle a higher monthly payment compared to the 30 year mortgage.
The EAR values for each loan are as follows:
20 and 30 year, Bullet loan: =effect(.061,12) = 6.27%
Smart loan: =effect(.061,24)= 6.28%
Int. only loan: =effect(.035,12) = 3.56%
Based on these numbers, they should go with the interest only loan, however, that could be very risky. It is almost impossible to know where the company will stand financially in ten years, there is a chance they will not be able to obtain the financing to pay their remaining principal after the ten years. Even after comparing the EAR values, I say it is safest to go with the 20 year loan.