Suppose you are evaluating an opportunity to purchase a condominium in central Bozeman. The condominium has an asking price of $455,500. Your first step in deciding whether to buy the property is to identify your best financing alternative. The condominium is a 2-story unit, with 3 bedrooms and 2 baths. It also has a deck, patio, and a 1-car attached garage with storage space. Assume that this property will be your primary home. Assume that you will have $85,000 available for a down payment and that you plan to close on your loan within 30 days. You are not selling a home in preparation of buying this property.

A common way to identify a set of loan opportunities available to you is via the internet. Useful websites include: LendingTree.com, BankLoans.com, BankRate.com, Amerisave.com, and QuickenLoans.com. The problem with using any of these clearinghouses is that you will subsequently be bombarded by numerous loan agents, trying to get your business. Feel free to investigate any of these sources if you are interested. However, in an attempt to standardize this project, as well as to reduce your search costs, I have assembled the following data on various loan alternatives.

You are to thoroughly evaluate each of the following loan types (assuming monthly payments on all loans):

1. Adjustable-Rate 3/1 30-Year Mortgage Loan: Initial Rate = 2.875%, Index = Rate on US Treasury Security with same Time-to-Maturity as that of the Outstanding Loan (e.g., at t=36, the Index is the rate on the 27-year US Bond; at t=48, the Index is the rate on the 26-year US Bond; and so on), Margin = 250%.
2. Adjustable-Rate 5/1 30-Year Mortgage Loan: Initial Rate = 3.000%, Index = Rate on US Treasury Security with same Time-to-Maturity as that of the Outstanding Loan (e.g., at t=60, the Index is the rate on the 25-year US Bond; at t=72, the Index is the rate on the 24-year US Bond; and so on), Margin = 1.20%.
3. Fixed-Rate (625%) 30-Year Mortgage Loan
4. The Fixed-Rate loan in 3, with the option to pay 2 points to reduce the rate by 0.25%.
5. Fixed-Rate (3.125%) 15-Year Mortgage Loan with a $50,000 balloon payment.
6. Fixed-Rate (4.125%) 30-Year Mortgage Loan with Interest-Only payments for the first 10 years.

After you identify specific terms for each alternative, I want you to construct a summary table:

	Alternative #1	Alternative #2	etc.
Purchase Price
Loan Down Payment
Points
................
You Decide What Else Might Be Important to a Borrower in Comparing the Various Loan Alternatives & Report These Items

CREATE A MASTER AMORTIZATION SCHEDULE

Your goal in this project is to construct one master amortization schedule. At the top of your master schedule, you should list all input variables, including: initial loan balance, term of loan (in years), initial interest rate, separate index values for years 4 through 10, and an index value for years 11+. (For convenience, we assume that the rate that applies for a loans eleventh year will continue to apply until that loan matures).

You should also include cells for the balloon payment (if the loan has a balloon payment, entering $0 if not applicable) and for any points on the loan (stated as a percentage to three decimal points, entering 0.000% if not applicable). Do not include any calculations in your input variables section.

You will need nine interest-rate cells, one for the initial three years of the loan term, one for each of years 4 through 10, and one more for years 11 and after. This will allow you to change the interest rate assumptions, depending on the type of loan. If the loan is fixed-rate, just type the fixed rate into all of the interest rate input cells. If the loan is a 3/1 ARM loan, type the starting rate into the cell for years 1-3, then enter unique rates into the cells for years 4 to 10 and 11+. If the loan is a 5/1 ARM loan, then the initial rate will apply for the first 5 years, and the rate for years 6 through 30 will be based on the index that prevails at that time (as described later in the instructions).

The amortization schedule should include the following columns:

1. Time should start at t = 0 and continue through t = 360. Loan terms are generally never longer than 360 months. Of course, you need to show time 0, for cash-flow purposes. But, obviously, the time-0 line should not be part of your loan amortization schedules.
2. Beginning balance is the loan balance at the beginning of the period.
3. Applicable annual rate (for calculating payments, etc.): I want you to include an additional column in the schedule that shows the annual interest rate that is expected to prevail at each future period in time (t = 1 through 360). You do not need to put any IF functions into your Annual Rate column. You should just absolute-reference the rate for months 1-12 to the appropriate input cell, the rate for months 13-24 to the appropriate input cell, and so on.
4. Monthly payment: Within this master schedule, I want you to focus first on getting the payment formula correct. For a given line in any amortization schedule, the payment should then drive all other values. The challenge here is that I want you to utilize a nested if function to calculate the payment. One part of your if function must look to the input variables and see whether the interest-only box contains a yes or is blank. If the loan does not contain an interest-only phase, then your if function should calculate payment for that period using: beginning balance (from column (2), number of monthly payments remaining (using a combination of the loan term in the inputs section and the time variable in column (1), the appropriate monthly interest rate (using the values in column (3) divided by 12, and the balloon payment (from the input cells, if applicable). If the loan does contain an interest-only phase, then you need to employ a nested if function that checks whether your time value in column (1) is greater than the point in time when the loan stops being interest-only. If the loan is an interest-only loan for at least part of its life, and if you are still in the interest-only phase, your payment calculation is simply equal to prevailing monthly interest rate times the beginning-of-period loan balance. If the loan is an interest-only loan for at least part of its life, and if you are past the interest-only phase, a given monthly payment is calculated using the beginning balance, number of months remaining, the prevailing monthly interest rate, and the balloon payment (from the input cells, if applicable).
5. Interest payment: A given months interest expense is simply equal to the periods beginning balance multiplied by the prevailing monthly interest rate.
6. A months principal payment equals the total payment minus the interest payment.
7. The ending balance equals the beginning balance minus the principal payment.
8. To the right of your amortization schedule you should include a column for cash flows, allowing for a cash flow at t=0 all the way through a cash flow at t=360, from the perspective of the borrower. The cash flow (CF) cell for t=0 should account for points. In each CF cell for t= 1360, you should include an if function that allows for the possibility that any CF contains both a final monthly payment and a balloon payment. If the loan does not have a balloon payment, use the if function to set the cash flow equal to the monthly payment; if the loan has a balloon payment, set the cash flow appropriately. Your if should also allow for the possibility that t is beyond the termination date, in which case the function should return a $0.00 in the cash-flow column.

FORMATTING: Every dollar amount in your analysis should show to the nearest $0.01; every percentage should be reported to the one one-thousandth of a percentage.

MONTHLY INTERNAL RATE OF RETURN / EFFECTIVE MONTHLY YEILD

Next, within the master spreadsheet, I want you to construct a cell (by utilizing Excels built-in function for calculating IRR that will report the monthly IRR (a.k.a., monthly effective yield), taking into account the net cash flow at t=0 and all of the future cash flows from t=1360. (For a 15-year loan, your cash flows from t=181360 should show up as $0.00 and will not harm your IRR calculation.)

INTEREST RATES FOR YOUR FORECASTED INDEX VALUES

To forecast the future cash flows for the two upcoming adjustable-rate mortgage (ARM) loans, you need a forecast of the future interest rates. Use the following rates:

27-year rate, 3 years from now	2.15%
26-year rate, 4 years from now	2.31%
25-year rate, 5 years from now	2.45%
24-year rate, 6 years from now	2.53%
23-year rate, 7 years from now	2.57%
22-year rate, 8 years from now	2.63%
21-year rate, 9 years from now	2.68%
20-year rate, 10 years from now	2.75%

NOTE: For year 11-30, you will repeat the 20-year rate used in year 10.