Question

"You are thinking of purchasing a house. The house costs $350,000. You have $50,000 in cash that you can use as a down payment on the house, but you need to borrow the rest of the purchase price. The bank is offering a 30-year mortgage that requires annual payments and has an interest rate of 7% per year. You can afford to pay only $23,500 per year. The bank agrees to allow you to pay this amount each year, yet still borrow $300,000. At the end of the mortgage (in 30 years), you must make a balloon payment; that is, you must repay the remaining balance on the mortgage. How much will this balloon payment be? Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section." House price $350,000 Down payment $50,000 Loan amount $300,000 Number of periods 30 Interest rate 7% Annual payment $23,500

PV of payments ?

Loan - PV of payments ?

Balloon payment ?

Answer 1

Ok, This is far simpler to calculate on an excel sheet without going too deep into fixed income formulas:

What we need to do is just create columns for Year, Repayment Amount, Interest Rate, Present Value Factor and Present Value. Check below screenshot. See both screenshots i.e. first and second one in conjunction with each other. The second one is a more detailed one. The first one is only to build your concepts on present value factor and present value, for calculation regarding baloon payment check screenshot 2.

SUM E X fc =1/(1.07)^B2 А Bc Year End Repayment Interest Rate Present Value Factor #Present Value $23,500.00 0.07 =1/(1.07)^B2 $21,962.62 $23,500.00 0.07 0.873438728 $20,525.81 $23,500.00 0.07 0.816297877 $19,183.00 $23,500.00 0.07 0.762895212 $17,928.04 $23,500.00 0.07 0.712986179 $16,755.18 $23,500.00 0.07 0.666342224 $15,659.04 $23,500.00 0.07 0.622749742 $14,634.62 $23,500.00 0.07 0.582009105 $13,677.21 $23,500.00 0.07 0.543933743 $12,782.44 $23,500.00 0.07 0.508349292 $11,946.21 $23,500.00 0.07 0.475092796 $11,164.68 $23,500.00 0.07 0.444011959 $10,434.28 $23,500.00 0.07 0.414964448 $9,751.66 $23,500.00 0.07 0.387817241 $9,113.71 $23,500.00 0.07 0.36244602 $8,517.48 $23,500.00 0.338734598 $7,960.26 $23,500.00 0.07 0.31657439 $7,439.50 $23,500.00 0.07 0.295863916 $6,952.80 $23,500.00 0.07 0.276508333 $6,497.95 $23,500.00 0.07 0.258419003 $6,072.85 $23,500.00 0.07 0.241513087 $5,675.56 $23,500.00 0.07 0.225713165 $5,304.26 $23,500.00 0.071 0.210946883 $4,957.25 $23,500.00 0.07 $4,632.95 $23,500.00 0.07 0.184249178 $4,329.86 $23,500.00 0.07 0.172195493 $4,046.59 $23,500.00 0.07 0.160930367 $3,781.86 $23,500.00 0.07 0.150402212 $3,534.45 $23,500.00 0.07 0.140562815 $3,303.23 $23,500.00 0.07 0.131367117| $3,087.13 32 Refer to Note 30 0.07 0.131367117 $291,612.47 $300,000.00 $8,387.53 Remaining Amount $63,848.03 * Present Value Factor = 1/(1+0.07)^Year Note: This is how much balloon payment will be:

Column F: Present Value is calculated as Present Value Factor x Repayment Amount (Column F = Column E x Column C)

B47 E x f =8387.53/0.131367117 ДА G H I J K L M N $23,500.00 $23,500.00 $23,500.00 $23,500.00 $23,500.00 $23,500.00 $23,500.00 $23,500.00 $23,500.00 $23,500.00 $23,500.00 $23,500.00 $23,500.00 $23,500.00 $23,500.00 $23,500.00 0.071 0.07 0.07 0.07 0.07 0.07 0.07 0.07 0.07 0.07 0.07 0.07 0.07 0.07 0.07 0.07 0.07 0.362446021 0.338734598) 0.31657439 0.295863916 0.276508333 0.258419003 0.241513087 0.225713165 0.210946883 0.19714662 0.184249178 0.172195493 0.160930367 0.150402212 0.140562815 0.131367117 0.1313671171 F $8,517.48 $7,960.26 $7,439.50 $6,952.80 $6,497.95 $6,072.85 $5,675.56 $5,304.26 $4,957.25 $4,632.95 $4,329.86 $4,046.59 $3,781.86 $3,534.45 $3,303.23 $3,087.13 32 Refer to Note 30 $291,612.47 Total Repayment after 30 years without considering Baloon payment (Sum F2:F31) $300,000.00 Total needed to be paid $8,387.53 Present value remainder of amount paid and amount needed to be paid (F34-F33) $63,848.03 Cell E34-E33 Remaining Amount** * Present Value Factor = 1/(1+0.07) Year Note: This is how much balloon payment will be: **Calculation of Remaining Amount Now as we know that the mortgagee needs to pay $8,387.53 still to settle the loan and also note that is present value of payment but the same needs to be paid at the end of 30th year, and any amount paid at the end of 30th year will have present value factor of 0.131367117 (check cell E31) then to arrive at present value, we need to = $8,387.53/0.131367117 $63,848.02

Now, as a final step, lets plug in the value of 63,848.02 in 30th year and check whether the sum of all future values add up to $300,000. Again taking help of our excel:

F36 2 A B C F F G H I J K L M N 0.07 0.07 0.07 Present Value Present Value Year End Repayment Interest Rate Factor of payment $23,500.00 0.07 0.934579439 $21,962.62 $23,500.00 0.071 0.873438728 $20,525.81 $23,500.00 0.07 0.816297877 $19,183.00 $23,500.00 0.07 0.762895212 $17,928.04 $23,500.00 0.07 0.712986179 $16,755.18 $23,500.00 0.07 0.666342224 $15,659.04 $23,500.00 0.622749742 $14,634.62 $23,500.00 0.07 0.582009105 $13,677.21 $23,500.00 0.07 0.543933743 $12,782.44 $23,500.00 0.07 0.508349292 $11,946.21 $23,500.00 0.07 0.475092796 $11,164.68 $23,500.00 0.444011959 $10,434.28 $23,500.00 0.07 0.414964448 $9,751.66 $23,500.00 0.07 0.387817241 $9,113.71 $23,500.00 0.36244602 $8,517.48 $23,500.00 0.07 0.338734598 $7,960.26 $23,500.00 0.07 0.31657439 $7,439.50 $23,500.00 0.07 0.295863916 $6,952.80 $23,500.00 0.276508333 $6,497.95 $23,500.00 0.07 0.258419003 $6,072.85 $23,500.00 0.07 0.241513087 $5,675.56 $23,500.00 0.07 0.225713165 $5,304.26 $23,500.00 0.07 0.210946883 $4,957.25 $23,500.00 0.07 0.19714662 $4,632.95 $23,500.00 0.07 0.184249178 $4,329.86 $23,500.00 0.07 0.172195493 $4,046.59 $23,500.00 0.07 0.160930367 $3,781.86 $23,500.00 0.150402212 $3,534.45 $23,500.00 0.07 0.140562815 $3,303.23 $23,500.00 0.07 0.131367117 $3,087.13 32 Refer to Note 30 $63,848.00 0.131367117 $8,387.53 $300,000.00 Total Repayment after 30 years without considering Baloon payment (Sum F2:F31) $300,000.00 Total needed to be paid Cell E34-E33 $0.00 Present value remainder of amount paid and amount needed to be paid (F34-F33) Remaining Amount** $0.00 0.07 0.07