Problem 1 (20 points). You are a sophomore now in college and you intend to borrow $5,000 on September 1, 2021 (start of the third year at college) and another $10,000 at the start of your senior year on September 1, 2022 (start of the last year, i.e., senior year at college). The first loan will accumulate interest for 20 months (bank computes your interest monthly) at T-bill rate of 4.3% (this is an annual rate) + a premium of 1.7% (annual rate) until May 1, 2023 (your graduate time). The second loan will accumulate interest for 8 months at T-bill rate of 4.3% (this is an annual rate) + a premium of 1.7% (annual rate) until May 1, 2023. After graduation, you have 9-month of grace period, during which you do not need pay your loan, but the interest on both of your loans will cumulate at a normal rate, which is T-bill rate of 4.3% + a high premium of 2.7% (annual rate), which applies to all periods till you pay off your loans in 10 years. In other words, you will make your first monthly payment on March 1, 2024 (i.e., one month later after the grace period ends), and make your last payment on Feb 1, 2034, which means that you will make 120 equal payments starting March 1, 2024 until Feb 1, 2024. For both loans, you must pay an initial fee of 4%, which means that if you borrow $100, you can only use $96 toward your tuition. Note you are required to complete all cells marked in color. Below is my grading guideline.

1. In cells B15 and B16, compute the principal (i.e., the money you owe to the bank) of your two loans at your graduation (4 points).
2. In Cell B19, compute the principal (total amount of money you owe to the bank) at the end of grace period (on Feb 1, 2024) (4 points)
3. In cell B21, use PMT function to compute your monthly payment, i.e., you will pay off the total amount of money you owe (in B19) in 10 years and do it monthly, and the first payment is one month later after grade period. The annual rate during this period is the T-bill rate + a high premium of 2.7%. (4 points)
4. In Cell B23, use IRR function to compute the monthly cost (%) of the two loans. In order to use IRR to compute the monthly rate, you need to fill all cash flows correctly from B29 to B178 (4 points)
5. In Cell B24, compute your effective annual cost of these two loans (%) (4 points)

A B D E F G H J K Problem 1: STUDENT LOAN Assumption: you borrow $5,000 on Sept 1st, 2021 (start of third year of college) and another $10,000 on Sept 1st, 2022 (start of last year of college) 1 Months to graduation 20 8 8 loan - initial fee