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)

AutoSave OFF AHSU- Excel 1 Home Insert Draw Page Layout Formulas Data Review View Developer Share Comments 21 7 Show Detail Lol Connections Properties Edit Links DE HE Clear Reply Advanced Refresh Geography From From New Database HTML Text Query 2 Sort Filter Graa Text ta Flash Columns Fill kerrave Data Consalidate Duplicates Voldation What if Analysis Urgroup Subtatal Hde Detail Data Analysis Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. Check for Updates D11 x A B C D E F H 1 J L M N O Problem 1: STUDENT LOAN Assumption: you borrow $5,000 on Sept 1st, 2021 (start of third year of college) ard another $10,000 on Sept 1st, 2022 (start of best year af calege) Months 2 Amount borrowed Date to graduation Junior year (Sept 1st, 2021 5,000.00 01 Sep 20 23 4 Senior year (Sept 1st, 2022 10,000.00 01 Sea 22 5 Inicial Fee 01-May-23 G 4.30% 1.70% 2.70% 0.00 C. SUM(B15:31 C. 7 Graduation dule B Grace period (months) 9 10 T-bil Interest rate 11 Premium over Treasury bir urval graduation 12 Premium over Treasury bill rate after graduation 13 14 Principal of loans at graduation (May 1, 2023) 15 The 1st loan (borrowe at the start of third year) 16 The 2nd loan (borrowed at the stard of senior year) 17 Total Principal at graduation 18 19 Principal art and at grace period (9-month later) 20 21 Monthly repayment 22 23 Use IRR of monthly cost (%) 24 Effective annual interest rate (EAIR) 26 26 27 Calculating the IRR of the loan 28 Month 29 91/21 30 10/1/21 31 11/1/21 32 12/1/21 33 1/1/22 34 21122 35 3/1.22 36 4/1.22 37 511122 38 6/1.22 39 711122 40 811122 Cash flow