Loan Strategies

An amortization schedule is a table that lists the principal and interest charges, and the remaining balance for each payment across the life of a loan. Lenders use this type of schedule to determine monthly payments for most loans including mortgages. A portion of each payment is applied to the principal balance of the loan and the remainder of the payment goes toward interest. In the beginning of a loan, the interest portion of a payment is larger, and as the loan matures the portion allocated to the principal increases. The key formulas used to calculate an amortization schedule can be found easily on the internet.

Suppose that Isabel and Chris are very responsible first-time home buyers. They have a down payment of $120,000 which is 20% of a $600,000 home that they hope will help them avoid private mortgage insurance (PMI). PMI is insurance to cover the lender in case the buyer is not able to repay the loan. With their down payment, Isabel and Chris have committed to stick to a sales price of $600,000 or less. They are working with a loan officer who has presented them with two different options. Plan A is that they borrow $480,000 on a 30-year fixed rate loan at an interest rate of 6%. Plan B is that they pay four points (4% of the loan) to get a lower interest rate, and then they roll the point amount into the loan amount. This means that they borrow $499,200 on a 30-year fixed rate loan to get a smaller interest rate of 5.5%.

Isabel and Chris feel confused, so they call their parents and ask for advice. To make matters worse, their parents say that no matter what plan they choose they should add an additional $150 to their payment every month so that they can pay off their loan earlier. This gives Isabel and Chris two more scenarios. Plan C is that they borrow $480,000 on a 30-year fixed rate loan at an interest rate of 6%, and they add an additional $150 each month to their principal. Plan D is that they pay four points (4% of the loan) to get a lower interest rate of 5.5% rolling it into the loan amount totaling $499,200, and they add an additional $150 each month to their principal.

a. Write a SAS program that uses a DATA step to create an amortization schedule for plans A and B. The program should create the following variables for each observation: time period (by month), beginning balance, payment amount, interest amount for that payment, principal for that payment, and end balance.

b. Create an amortization schedule for the $150 pre-payment plans C and D. Make sure that for each schedule only the time periods where there is an actual balance get output to the data set. In other words, scenarios C and D will allow them to pay the loan off sooner than 30 years, so only print the applicable time points. In this way, they will know how early the loan will be paid off. Note that the end balance for the last time point is most likely somewhat less than the regular payment. In this case, adjust the last payment accordingly. Also, throughout the schedule, the payment amount variable should include the $150 extra that they will be applying toward their principal balance.

c. Once Isabel and Chris think that they have decided on their plan, they get another phone call from their parents. Oh, by the way, dont forget that the amount of interest that you pay each year can be a deduction on your taxes. You might also want to review this before you decide on a plan, her father says. They thought they had it all figured out, but now they are back to square one. Use your programming from parts a) and b), to calculate the amount of interest paid annually across the total time frame of the loan. Because Isabel and Chris are already feeling overwhelmed, they would like to make a quick comparison across the four plans, and they do not want to see the entire amortization schedule for each one. Instead, they would like to have a summary with one row per plan (for a total of four observations) with a column for each years interest. This means adding 30 variables to the data set that contains the total amount of interest paid for each of the 30 years (using the interest variable created in the amortization schedule). If there is no interest paid during the year for plans that are paid off early, then the interest should appear as zero.

d. They would also like to know the number of years until the loan is paid off. This can be calculated in the data set created in part c). Since the number of years will not always be an integer for plans C and D, it will be easier for them to understand if you create two variables, one for year (1 to 30) and one for month (1 to 12). Ultimately, the final summary data set should have variables for the plan (A, B, C, and D), payment amount, interest in each of the 30 years, year paid off, and month paid off.

e. Create a summary PDF report for Isabel and Chris to review. Incorporate the amortization schedules for the four plans created in parts a) and b) and the interest summary created in parts c) and d). Only print relevant variables and not extraneous information that may have been created along the way. Be sure to label and format the report appropriately.