 Okay, welcome to the audio and video instructions for Worksheet D, T-bills and mortgages. At this point, you should have good familiarity with the way Excel is working and making models based on the templates I've been providing. So this worksheet is slightly more open-ended in the sense that I didn't provide all the face value, interest rate, gear value here, that you need to write that, you need to type that in. Do it in a way that makes it look nice, show that you really have mastered creating models in Excel. So the first part, you need to create a model that calculates the price of a zero coupon bond. If you know the yield to maturity, the face value and the time to maturity, that's the first model. I'll create it right here on this page. And the second model, create it right below, is slightly different. You'll have to rearrange the formula a little bit to create a model that calculates the yield of a zero coupon bond. If you know the price, the face value and the time to maturity, that should be fairly straightforward if you've been following along so far. Mortgages are a little bit less straightforward. This is a little bit of a more challenging sheet, there's a little more guidance here. You need to figure out a formula for calculating the loan that a person can afford, if the total amount of the loan that a person can afford, if they can afford a $1,000 monthly payment assuming that the interest rate is 5%. So your model should calculate the total loan amount if the monthly payment is $1,000 and the interest rate is 5%. This is a 30-year fixed, for a 30-year fixed rate mortgage. You're going to need to use the appendix to chapter 4 to figure this out. And you're going to need to look carefully at my hints up here because I want you to make this worksheet work given the standards of the mortgage industry. And that's where this hint 2 comes in. Point 2 says mortgage interest rates are quoted in a funny way. When the mortgage industry says that your yearly interest rate is 5%, what they really mean is that your monthly interest rate is 5 divided by 12, or perhaps a little bit more clear. If they say that your yearly interest rate is 12%, that means that your monthly interest rate is 1%. So in order to make this model work, you need to make sure that in your formula that you enter the interest rates in a monthly time period and that you calculate the formula for the total number of months being 360 months, not 12 years. In other words, your model should not calculate 12 years at 5% or whatever per year. It should calculate 360 months at 5% divided by 12 months. If you do it that way, then when you go online and you type in say mortgage calculator in Google and you enter this information, what you'll get on your mortgage calculator will be in Google on whatever website you feel like should be the way that this model works out. So the cool thing you should be able to do is create a mortgage calculator instead of having to go online. You should be able to create one right here that you can use. In order to make sure you've done it right, because I know that this is going to be challenging, you're going to have to play around with it, I have provided solutions for you. This isn't the formula, that's the trick, you need to figure that out. But your formula, if you enter these parameters should yield this result. If you get that, you'll know you did the formula correctly. In other words, at an interest rate of 5% and a monthly payment of $1,000, that person can afford a $186,000 loan. Conversely, the second part of the assignment asks you, if you know the amount of loan that you'd like to take out and you know the interest rate, you can calculate your monthly payment. So think about it, when you get a mortgage, there's sort of two ways you can think about it. You have a budget, say $1,000, and you ask yourself, well how much house can I afford with $1,000? That's what this first calculation gives you. The second calculation says, okay, gee, I'd like a house that costs or that I'm going to need $100,000 mortgage to be able to afford, how much is my monthly payment going to be? That's what this payment, that's what this model gives you. Obviously the two are very related. For the purposes of this class, there's some different economic meaning that we associate with that. That is going to be drawn out down here in this section of the assignment. What I'd like you to do down here is using the models you've developed up here, create some data tables. Up until this point in the class, you've always created a two by two data table. In other words, you're varying something in the row and in the column. This data table is only asking you to vary the interest rate. It's saying, hey, listen, leave the monthly payment fixed. In other words, what this data table is going to show you is assuming that you have $1,000 monthly payment. How much loan can you afford if the interest rate is 7% and what happens as the interest rate on mortgages falls? What you should notice is as interest rates fall, the amount of loan you can afford increases. The point of this is to show you that falling interest rates put upward pressure on home prices because they enable borrowers to spend more money on a fixed income. The next one is asking, well, what happens to your monthly payment if interest rates change? And this is particularly relevant for types of mortgages that have adjustable interest rates. Now, you've done this calculation for a 30-year fixed rate mortgage. But just to give you an idea, there is such a thing as an adjustable rate mortgage. That means that your mortgage payment will change depending on what the prevailing interest rate in the economy is, and it will adjust every month to that interest rate. So the message of this model is, look, if interest rates are low when you take out this mortgage and then they rise, your payments are going to start going up. And that's going to be a problem for you if you can't afford the higher payments if you can only afford the lower payments. And we're going to talk much more about why that mattered for the financial crisis. But this model should just give you some intuitive sense about the relationship between interest rates and monthly payments and interest rates and the amount of house that you can afford. Again, though, the challenge with this spreadsheet is figuring out the right formula in here. And let me tell you, it's not simple. It's not an easy formula. But if you focus on that appendix, you should be able to work it out. Again, I provide the answers to the right.