Real Estate Investing, Loan Amortization Schedule workout. On the workout tab, it asks us to calculate the payoff for the following loan, according to the terms below. We have a 10 million loan. We have a 30 year amortization period. We have an annual rate of 4.3%, but we have a term for this loan, meaning that the loan is due in 10 years. And as a result, we will need to determine what that balloon payment is going to be at year 10, or month 120, 'cause this is going to be a monthly schedule. I have named these cells, because we will copy them extensively throughout the model, and it's easier to have them named. The first thing we're gonna do is calculate our ending balance of the loan going into this forecast period, which is effectively the $10 million. This is the ending balance at time zero, which becomes the beginning balance in month one. The total payment is a combination of the principle and the interest, and that's the classic monthly payment amount for a mortgage schedule. However, what we need to do is determine the principle amount and the interest amount. And that's because in the final period, or the balloon payment period, we're gonna need to repay only the principle. So the total payment will just be a function of the principle and the interest. The interest is calculated very easily as the monthly rate times the beginning balance, and that has to be negative. So we'll flip that to a negative. So what that leaves us next is the principle payment. What the principle payment is going to be is a function of whether or not we are in that period where the balloon payment occurs. We also wanna make sure that if we are beyond the balloon payment that we are not calculating any further principle payments. So what we're gonna do is we're gonna do a nested IF statement, so that we can have basically three situations. What happens if we are before the repayment period? What happens if we are at the repayment period? And then what happens beyond? The period that I'm in is greater than the term times 12, and I'm starting with the greater than because it's the easiest one. Term times 12, that's gonna be zero. There's simply no principle repayment beyond month 120. Now I'm going to nest, and I'm gonna say if, open parenthesis, C15 is equal to the term times 12, now I want to repay the opening balance, and I want it as a negative, 'cause it's a repayment. And now if it's not, I'm going to use a formula called the Principle Payment Formula, P Payment, which is like the Payment Formula, but that combines the interest in the principle. This P Payment strips out the principle amount, and it works pretty much the same way. We take our rate divide by 12, it then asks us what period are we in, because the principal amount will change from period to period. It will then ask us how many total periods are in the loan, which are the amortization period times 12 for monthly. The present value, which I'll keep as positive, so it returns a negative value at the end, and I'll anchor that. The future value is zero, and the discount type is zero. And I'll close the parenthesis, and I get a negative principle repayment of 13,777.3. The total payment therefore made each month is the combination of the two. But my ending balance is the beginning balance minus only the principle repayment. And if I go ahead now and copy this all the way down, what we should see is that beyond month 120, nothing is being paid, and that's true. But let's look at month 120, and see if what happens is actually what we want to have happen. The principle amount in column F, it knows that we are in month 120, so it's repaying the opening balance. We are also paying a full year of interest on that, because we're assuming that the repayment is coming at the end of the year, so the interest is being calculated and my total payment is the sum of those two. But the end of year balance is zero, and then that carries on through the end of the model. So the model is working perfectly, and what we need to do now is simply go up to our balloon payment. And of course we can always and find our balloon payment, but it's nice to use the vlookup function here as well. And the vlookup function will tell us what it is that we want to see. And here we're gonna look up a, based on the month that we are in. So that's gonna be the term times 12, the table array is the entire table. And then the column index number is the column that the principle payment is in, and that's going to be column four. And then we want an exact match, so we're gonna put a zero in here. And what that's going to do is that's going to tell us what that principle amount is in 120, and that's the 7,965,307.0.