How would a loan ballon in year 5 affect IRR Use excel

Use the amortization schedule below to calculate the Lender's IRR. Assume the loan balloons in Year 5. Loan Principal Amortization Term Loan Term Interest Rate Lender Fee Lender IRR $1,200,000 25 5 10.00% 4.00% 10.53% Interest Principal 4 5 6 7 8 9 10 11 12 13 14 Begin $1,200,000 $1,200,000 $1,199,096 $1,198,184 $1,197,264 $1,196,337 $1,195,402 $1,194,459 $1,193,509 $1,192,550 $1,191,584 $1,190,609 $1,189,626 $1,188,636 $1,187,636 $1,186,629 $1,185,613 $1,184,589 $1,183,556 $1,182,515 $1,181,465 $1,180,406 $1,179,338 $1,178,261 $1,177,176 $1,176,081 $1,174,977 $1,173,865 $1,172,742 $1,171,611 PMT $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,000.00 $9,992.46 $9,984.86 $9,977.20 $9,969.47 $9,961.68 $9,953.83 $9,945.91 $9,937.92 $9,929.86 $9,921.74 $9,913.55 $9,905.30 $9,896.97 $9,888.58 $9,880.11 $9,871.57 $9,862.97 $9,854.29 $9,845.54 $9,836.71 $9,827.82 $9,818.84 $9,809.80 $9,800.68 $9,791.48 $9,782.20 $9,772.85 $9,763.42 End $1,200,000 $1,199,096 $1,198,184 $1,197,264 $1,196,337 $1,195,402 $1,194,459 $1,193,509 $1,192,550 $1,191,584 $1,190,609 $1,189,626 $1,188,636 $1,187,636 $1,186,629 $1,185,613 $1,184,589 $1,183,556 $1,182,515 $1,181,465 $1,180,406 $1,179,338 $1,178,261 $1,177,176 $1,176,081 $1,174,977 $1,173,865 $1,172,742 $1,171,611 $1,170,470 $904.41 $911.95 $919.55 $927.21 $934.93 $942.73 $950.58 $958.50 $966.49 $974.55 $982.67 $990.86 $999.11 $1,007.44 $1,015.83 $1,024.30 $1,032.83 $1,041.44 $1,050.12 $1,058.87 $1,067.70 $1,076.59 $1,085.56 $1,094.61 $1,103.73 $1,112.93 $1,122.20 $1,131.56 $1,140.99 15 16 Lender CF's ($1,152,000) $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 $10,904 17 18 19 20 21 22 23 24 25 26 27 28 29