Hey, I am having trouble figuring these out and want to see if I am using the correct formulas on mine in excel. Thank you for any help :)
Replicate the following loan analysis scenarios from Chapter 9 in our text book on an Excel Spreadsheet using financial formulas where applicable, and submit your work by the due date, Sunday 12/1: (15 Points Total) 1. Illustration 9-1 (5 Points) 2. Illustration 9-2/9-3 (using Excel)/9-4 (using Excel) (5 Points) 3. Illustration 9-10 Loan Amortization Schedule (5 Points) ASSIGNMENT SUBMISSION I/Y = 7.5 N= 20 PMT = 10,000 CPT PV PV=-$101,945 ILLUSTRATION 9-2 The Maximum Amount You Should Borrow Loan-to-value ratio The value of the restaurant building that you are mortgaging: $350,000 Lender's loan-to-value ratio: 65% Maximum possible loan from lender: $350,000 X 65% = $227,500 If you need a loan of $300,000, what loan-to-value ratio do you need to find in a lender? $300,000 / $350,000 = 86% Debt service coverage ratio The annual debt service from your loan to purchase a new fleet of golf carts is $125,000, and your annual cash flow is $400,000. What is your debt service coverage ratio? If you are about to seek a loan and know that most hospitality lenders today require a debt service coverage ratio of between 1.25 and 1.50, would you be able to obtain a loan? Debt service coverage ratio = Cash flow / Debt service = $400,000 / $125,000 = 3.20 You are well above the 1.25 to 1.50 range, so obtaining a loan should not be a problem. ILLUSTRATION 9-1 CHAPT ILLUSTRATION 9-4 Finding the Optimal Amortization Rate PV = -$110,000 PMT= $10,000 I/Y = 7.5% CPT N N= 24.1 years staurants X $25,000 per fryer = $200,000 firver = $200,000 purchase With a 20% down 20% down payment, the loan is therefore 80% $200,000 X 80% = $160,000 Terms of loan: 5%, 5-year en 1. Payment Calculation for Debt Service 160,000 (PV) 5 [N] 5 [1/Y] [CPT] [PMT) Answer computed as $36,955.9677 Step 2. Amortization Functions e the display shows the payment amount, you need to invoke the Amortization function AMORT which is above the IPVI button [20] [PV] Display shows Pl = 1.0000 P1 stands for the beginning of a period, and thus the calculator is now looking at the beginning of period 1 or the first year. If the display does not show Pl=1.0000, enter [1] [ENTER] Display shows P2 = 1.0000 P2 stands for the end of a period, and thus the calculator is now looking at the end of period 1. or the first year. If the display does not show Pl=1.0000, please enter [1] [ENTER]. Now, the calculator is looking at all values of Year 1 Display shows BAL = 131,044.0323 Display shows PRN = -28,955.9677 Display shows INT = -8.000.000,0 Display shows P1 = 1.0000 2 [ENTER] Display shows Pl = 2.0000 Display shows P2 = 1.0000 2 [ENTER] Display shows P2 = 2.0000 Display shows BAL = 100,640.2662 Display shows PRN = -30,403.7661 Display shows INT = -6,552.2016 Display shows P1 = 2.0000 3 [ENTER] Display shows P1 = 3.0000 Display shows P2 = 2.0000 3 [ENTER] Display shows P2 = 3.0000 Display shows BAL = 68,716.3118 th Display shows PRN = -31,923.9544 [] Display shows INT = -5,032.0133 Display shows P1 = 3.0000 4 [ENTER] Display shows Pl = 4.0000 Display shows P2 = 3.0000 4 [ENTER] Display shows P2 = 4.0000 Display shows BAL = Display shows P1 = 4.0000 [ENTER] Display shows P1 = 5.0000 [] Display shows P2 = 4.0000 SIENTER] Display shows P2 = 5.0000 Display shows BAL=0.0000, indicating loan has been paid off Display shows PRN = -35.196.1597. same as the balance left after Year 4 Display shows INT = -1,759.8080 Loan Amort ILLUSTRATION 9-10 Iculation Using a Business Calculator Loan Amortization Schedule Generated: Year Payment 36,956 36,956 36,956 36,956 36,956 Principal 28,956 30,404 31,924 33,520 35,196 Interest 8,000 6,552 5,032 3,436 1,760 Balance 131,044 100,640 68,716 35,196 0.00 ILLUSTRATION 9-10 (Continued) Replicate the following loan analysis scenarios from Chapter 9 in our text book on an Excel Spreadsheet using financial formulas where applicable, and submit your work by the due date, Sunday 12/1: (15 Points Total) 1. Illustration 9-1 (5 Points) 2. Illustration 9-2/9-3 (using Excel)/9-4 (using Excel) (5 Points) 3. Illustration 9-10 Loan Amortization Schedule (5 Points) ASSIGNMENT SUBMISSION I/Y = 7.5 N= 20 PMT = 10,000 CPT PV PV=-$101,945 ILLUSTRATION 9-2 The Maximum Amount You Should Borrow Loan-to-value ratio The value of the restaurant building that you are mortgaging: $350,000 Lender's loan-to-value ratio: 65% Maximum possible loan from lender: $350,000 X 65% = $227,500 If you need a loan of $300,000, what loan-to-value ratio do you need to find in a lender? $300,000 / $350,000 = 86% Debt service coverage ratio The annual debt service from your loan to purchase a new fleet of golf carts is $125,000, and your annual cash flow is $400,000. What is your debt service coverage ratio? If you are about to seek a loan and know that most hospitality lenders today require a debt service coverage ratio of between 1.25 and 1.50, would you be able to obtain a loan? Debt service coverage ratio = Cash flow / Debt service = $400,000 / $125,000 = 3.20 You are well above the 1.25 to 1.50 range, so obtaining a loan should not be a problem. ILLUSTRATION 9-1 CHAPT ILLUSTRATION 9-4 Finding the Optimal Amortization Rate PV = -$110,000 PMT= $10,000 I/Y = 7.5% CPT N N= 24.1 years staurants X $25,000 per fryer = $200,000 firver = $200,000 purchase With a 20% down 20% down payment, the loan is therefore 80% $200,000 X 80% = $160,000 Terms of loan: 5%, 5-year en 1. Payment Calculation for Debt Service 160,000 (PV) 5 [N] 5 [1/Y] [CPT] [PMT) Answer computed as $36,955.9677 Step 2. Amortization Functions e the display shows the payment amount, you need to invoke the Amortization function AMORT which is above the IPVI button [20] [PV] Display shows Pl = 1.0000 P1 stands for the beginning of a period, and thus the calculator is now looking at the beginning of period 1 or the first year. If the display does not show Pl=1.0000, enter [1] [ENTER] Display shows P2 = 1.0000 P2 stands for the end of a period, and thus the calculator is now looking at the end of period 1. or the first year. If the display does not show Pl=1.0000, please enter [1] [ENTER]. Now, the calculator is looking at all values of Year 1 Display shows BAL = 131,044.0323 Display shows PRN = -28,955.9677 Display shows INT = -8.000.000,0 Display shows P1 = 1.0000 2 [ENTER] Display shows Pl = 2.0000 Display shows P2 = 1.0000 2 [ENTER] Display shows P2 = 2.0000 Display shows BAL = 100,640.2662 Display shows PRN = -30,403.7661 Display shows INT = -6,552.2016 Display shows P1 = 2.0000 3 [ENTER] Display shows P1 = 3.0000 Display shows P2 = 2.0000 3 [ENTER] Display shows P2 = 3.0000 Display shows BAL = 68,716.3118 th Display shows PRN = -31,923.9544 [] Display shows INT = -5,032.0133 Display shows P1 = 3.0000 4 [ENTER] Display shows Pl = 4.0000 Display shows P2 = 3.0000 4 [ENTER] Display shows P2 = 4.0000 Display shows BAL = Display shows P1 = 4.0000 [ENTER] Display shows P1 = 5.0000 [] Display shows P2 = 4.0000 SIENTER] Display shows P2 = 5.0000 Display shows BAL=0.0000, indicating loan has been paid off Display shows PRN = -35.196.1597. same as the balance left after Year 4 Display shows INT = -1,759.8080 Loan Amort ILLUSTRATION 9-10 Iculation Using a Business Calculator Loan Amortization Schedule Generated: Year Payment 36,956 36,956 36,956 36,956 36,956 Principal 28,956 30,404 31,924 33,520 35,196 Interest 8,000 6,552 5,032 3,436 1,760 Balance 131,044 100,640 68,716 35,196 0.00 ILLUSTRATION 9-10 (Continued)
