2 A friend of your wishes to borrow $400,000 to refurbish a business. It is esti

Question

2 A friend of your wishes to borrow $400,000 to refurbish a business. It is estimated that it will take at least 6 months to do the remodeling so the business will be closed and not be generating any revenue during that time. Therefore, your friend is talking to a banker about obtaining a loan that will allow no payments for the first 8 months just in case it takes longer than 6 months to complete the project. But, even after the business reopens, your friend is not sure that the cash flows will allow full amortization payments on the loan. So, for the next 18 months, the payments will be interest only. Thereafter, your friend wishes to pay off the loan in equal monthly payments of $4000. The problem is that the bank will only allow this type of loan to have a term of at most 8 years, so at the end of the 8 years, your friend will be faced with a balloon payment that will have to be refinanced or paid off. Build the amortization table for this loan if the annual interest rate is 6.45% and calculate the size of the balloon payment that will be due 96 months after the loan is taken out. (Hints: you cannot use the IPMT, and PPMT functions throughout this spreadsheet. So, elegance would dictate that you do not use them at all.) You MUST figure out a way to use ONLY one formula that can be copied down from period 1 in each of the "Pmt to Interest," "Pmt to Principal," and "Balance Due" columns 2b. Copy the spreadsheet onto a new tab and provide an amortization table where the first 30 months are the same as above, but now your friend wants to make payments in months 27 through 96 so that the balloon payment after 8 years is $175,000 Learning Objectives: Students will learn to build irregular amortization tables for which the Excel financial functions are not always suited. Students will learn to interpret their results in a critical manner. In part b, students will employ the goal seek capabilities of Excel to solve the problem.

Explanation / Answer

The bank is allowing only 8 year period loan involving balloon payment

After considering that upto 18 months only interest amount is paid and from 19th Month 4000 per month is paid

Calculation of balloon payment

Principal oustanding at beginning of month

Column B

Interst for Month

(Column B*.5375%)

Column C

Payment

Column D

Principal Outstanding at month End

(B+C-D)

Cloumn E

answer for qusetion 2

Balloon payment is 175000 and friend wishes to pay EMIs upto 57th months from 31 months( upto 30 months emi is 4000 per month from 19th Month , upto 1st 18 month only interest amount is paid.

So balloon payment at 96th Month is 175000, from 58th Month onwards no emi is paid, interest is accruing monthly on loan amount outstanding from 58th Month

Future value of Amount is 175000

Present value of 175000 at 58th Month is ((175000)/(1+0.05735)^39)

* 0.05735 is (6.75%/12months) 39 is number of months from 58th month to 96th month

i.e., 141985

now the answer is

Balloon payment at the end of 96 th month is 175000

EMI from 31 to 57th month is 10,142.9

interest rate per month 0.5375 Month

Principal oustanding at beginning of month

Column B

Interst for Month

(Column B*.5375%)

Column C

Payment

Column D

Principal Outstanding at month End

(B+C-D)

Cloumn E

1 400000 2150 2150 400000 2 400000 2150 2150 400000 3 400000 2150 2150 400000 4 400000 2150 2150 400000 5 400000 2150 2150 400000 6 400000 2150 2150 400000 7 400000 2150 2150 400000 8 400000 2150 2150 400000 9 400000 2150 2150 400000 10 400000 2150 2150 400000 11 400000 2150 2150 400000 12 400000 2150 2150 400000 13 400000 2150 2150 400000 14 400000 2150 2150 400000 15 400000 2150 2150 400000 16 400000 2150 2150 400000 17 400000 2150 2150 400000 18 400000 2150 2150 400000 19 400000 2150 4000 398150 20 398150 2140.06 4000 396290 21 396290 2130.06 4000 394420 22 394420 2120.01 4000 392540 23 392540 2109.9 4000 390650 24 390650 2099.74 4000 388750 25 388750 2089.53 4000 386839 26 386839 2079.26 4000 384919 27 384919 2068.94 4000 382987 28 382987 2058.56 4000 381046 29 381046 2048.12 4000 379094 30 379094 2037.63 4000 377132 31 377132 2027.08 4000 375159 32 375159 2016.48 4000 373175 33 373175 2005.82 4000 371181 34 371181 1995.1 4000 369176 35 369176 1984.32 4000 367161 36 367161 1973.49 4000 365134 37 365134 1962.6 4000 363097 38 363097 1951.64 4000 361048 39 361048 1940.63 4000 358989 40 358989 1929.57 4000 356919 41 356919 1918.44 4000 354837 42 354837 1907.25 4000 352744 43 352744 1896 4000 350640 44 350640 1884.69 4000 348525 45 348525 1873.32 4000 346398 46 346398 1861.89 4000 344260 47 344260 1850.4 4000 342111 48 342111 1838.84 4000 339949 49 339949 1827.23 4000 337777 50 337777 1815.55 4000 335592 51 335592 1803.81 4000 333396 52 333396 1792 4000 331188 53 331188 1780.14 4000 328968 54 328968 1768.2 4000 326736 55 326736 1756.21 4000 324493 56 324493 1744.15 4000 322237 57 322237 1732.02 4000 319969 58 319969 1719.83 4000 317689 59 317689 1707.58 4000 315396 60 315396 1695.25 4000 313091 61 313091 1682.87 4000 310774 62 310774 1670.41 4000 308445 63 308445 1657.89 4000 306103 64 306103 1645.3 4000 303748 65 303748 1632.64 4000 301380 66 301380 1619.92 4000 299000 67 299000 1607.13 4000 296607 68 296607 1594.27 4000 294202 69 294202 1581.33 4000 291783 70 291783 1568.33 4000 289351 71 289351 1555.26 4000 286907 72 286907 1542.12 4000 284449 73 284449 1528.91 4000 281978 74 281978 1515.63 4000 279493 75 279493 1502.28 4000 276996 76 276996 1488.85 4000 274484 77 274484 1475.35 4000 271960 78 271960 1461.78 4000 269422 79 269422 1448.14 4000 266870 80 266870 1434.43 4000 264304 81 264304 1420.64 4000 261725 82 261725 1406.77 4000 259132 83 259132 1392.83 4000 256524 84 256524 1378.82 4000 253903 85 253903 1364.73 4000 251268 86 251268 1350.57 4000 248619 87 248619 1336.32 4000 245955 88 245955 1322.01 4000 243277 89 243277 1307.61 4000 240584 90 240584 1293.14 4000 237878 91 237878 1278.59 4000 235156 92 235156 1263.96 4000 232420 93 232420 1249.26 4000 229669 94 229669 1234.47 4000 226904 95 226904 1219.61 4000 224124 96 224124 1204.66 4000 221328 Balloon payment 221328 is Balloon Payment

Related Questions

Navigate

• Browse (All)
• Browse 2
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.