spreadsheet excercise on excel

PROTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View Enable Editing Eine Frau GHI AB Spreadsheet Exercise: Chapter 16 Your company is considering manufacturing protective cases for a popular new smartphone. Management decides to borrow $200,000 from each of two banks, First American and First Citizen. On the day that you visit both banks, the quoted prime interest rate is 7%. Each loan is similar in that each involves a 60-day note, with interest to be paid at the end of 60 days. The interest rate was set at 2% above the prime rate on First American's fixedrate note. Over the 60-day period, the rate of interest on this note will remain at the 2% premium over the prime rate regardless of fluctuations in the prime rate. First Citizen sets its interest rate at 1.5% above the prime rate on its floating-rate note. The rate charged over the 60 days will vary directly with the prime rate. To Do Create a spreadsheet to calculate and analyze the following for the First American loan: a. Calculate the total dollar interest cost on the loan. Assume a 365-day year. b. Calculate the 60-day rate on the loan. c. Assume that the loan is rolled over each 60 days throughout the year under identical conditions and terms. Calculate the effective annual rate of interest on the fixed-rate, 60-day First American note. Next, create a spreadsheet to calculate the following for the First Citizen loan: d. Calculate the initial interest rate. e. Assuming that the prime rate immediately jumps to 7.5% and after 30 days it drops to 7.25%, calculate the interest rate for the first 30 days and the second 30 days of the loan. 12 Chapter 16 READY The here to sear Milos Zutter Spreadsheet Chapter 16 Start (Protected View - Excel FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit. it's safer to stay in Protected View Enable Editing AB a. Calculate the total dollar interest cost on the loan. Assume a 365-day year. 200,000 Loan Amount Prime + Premium Maturity (Years) Total Dollar Interest b. Calculate the 60-day rate on the loan. 200,000 Loan Amount Total Dollar Interest 60-Day Rate c. Assume that the loan is rolled over each 60 days throughout the year under identical conditions and terms. Calculate the effective annual rate of interest on the fixed-rate, 60-day First American note. 0.0000% 60-Day Rate Number of 60-Day Periods/Year Effective Annual Rate Chapter 16 M U LLIP 16 Start Protected View] - Excel HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW OTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit. it's safer to stay in Protected View Enable Editing fr B D Next, create a spreadsheet to calculate the following for the First Citizen loan: d. Calculate the initial interest rate. Prime Rate Premium Initial Rate, First 30-Day Rate 7.00% 1.50% e. Assuming that the prime rate immediately jumps to 7.5% and after 30 days it drops to 7.25%, calculate the interest rate for the first 30 days and the second 30 days of the loan. 0.00% 30 Initial Rate + Premium Number of Days Maturity (Years) First 30-Day Rate Initial Rate + Premium Last 30-Day Rate 1. Calculate the total dollar interest cost. non Chapter 16 mm Type here to search o e 9 w x] PROTECTED VIEW Be careful files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View Enable Editing fi AB f. Calculate the total dollar interest cost. Loan Amount First 30-Day Rate Last 30-Day Rate Total Interest Cost 200,000 0.0000% 0.0000% g. Calculate the 60-day rate of interest. 200,000 Loan Amount Total Interest Cost 60-Day Rate h. Assume that the loan is rolled over cach 60 days throughout the year under the same conditions and terms. Calculate the effective annual rate of interest. 60-Day Rate Periods in Year Effective Annual Rate 0.0000% 6.08333 Chapter 16 Type here to search Enable Editing FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW PROTECTED VIEW Be careful files from the Internet can contain viruses. Unless you need to edit it's safer to stay in Protected View 429 1x & fr i. Which loan would you choose, and why? because it has the lowest effective rate. Points 88 Requirements 1 In cell E31, by using cell references to the given data, calculate the total interest rate (prime interest rate plus the interest rate premium) 2 In cell E32, by using cell references to the given data, calculate the loan maturity in years. 3 In cell E33, by using cell references to the given data, calculate the total interest payment. 4 In cell E39, by using cell references to the given data, calculate the 60-day interest rate. 5 In cell E44, by using cell references to the given data, calculate the number of 60-day periods in a year. 6 In cell E45, by using cell references to the given data, calculate the effective annual interest rate. 7 In cell E52, by using cell references to the given data, calculate the initial interest rate (first 30-day interest rate) 8 In cell E58, by using cell references to the given data, calculate the maturity in years. 9 In cell E59, by using cell references to the given data, calculate the first 30-day interest rate. 10 In cell E61, by using cell references to the given data, calculate the interest rate corresponding to the initial interest rate plus the premium rate. 29 11 In cell E62, by using cell references to the given data, calculate the last 30 day interest rate. 00 12 In cell E69. by using cell references to the given data, calculate the total interest cost. Chapter 16 Type here to search Milios Zutter Spreadsheet Chapter 16 Start (Protected View] - Excel LE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit. it's safer to stay in Protected View Enable Editing A D - X fo B H 8 In cell E58, by using cell references to the given data, calculate the maturity in years. 9 In cell E59, by using cell references to the given data, calculate the first 30-day interest rate. 10 In cell E61, by using cell references to the given data, calculate the interest rate corresponding to the initial interest rate plus the premium rate. 11 In cell E62, by using cell references to the given data, calculate the last 30-day interest rate. 12 In cell E69, by using cell references to the given data, calculate the total interest cost. 13 In cell E75, by using cell references to the given data, calculate the 60-day interest rate. 14 In cell E81, by using cell references to the given data, calculate the effective annual interest rate. 15 In cell C85, type either First Citizen or First American depending on which loan choice is better. 16 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. Chapter 16 PROTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View Enable Editing Eine Frau GHI AB Spreadsheet Exercise: Chapter 16 Your company is considering manufacturing protective cases for a popular new smartphone. Management decides to borrow $200,000 from each of two banks, First American and First Citizen. On the day that you visit both banks, the quoted prime interest rate is 7%. Each loan is similar in that each involves a 60-day note, with interest to be paid at the end of 60 days. The interest rate was set at 2% above the prime rate on First American's fixedrate note. Over the 60-day period, the rate of interest on this note will remain at the 2% premium over the prime rate regardless of fluctuations in the prime rate. First Citizen sets its interest rate at 1.5% above the prime rate on its floating-rate note. The rate charged over the 60 days will vary directly with the prime rate. To Do Create a spreadsheet to calculate and analyze the following for the First American loan: a. Calculate the total dollar interest cost on the loan. Assume a 365-day year. b. Calculate the 60-day rate on the loan. c. Assume that the loan is rolled over each 60 days throughout the year under identical conditions and terms. Calculate the effective annual rate of interest on the fixed-rate, 60-day First American note. Next, create a spreadsheet to calculate the following for the First Citizen loan: d. Calculate the initial interest rate. e. Assuming that the prime rate immediately jumps to 7.5% and after 30 days it drops to 7.25%, calculate the interest rate for the first 30 days and the second 30 days of the loan. 12 Chapter 16 READY The here to sear Milos Zutter Spreadsheet Chapter 16 Start (Protected View - Excel FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit. it's safer to stay in Protected View Enable Editing AB a. Calculate the total dollar interest cost on the loan. Assume a 365-day year. 200,000 Loan Amount Prime + Premium Maturity (Years) Total Dollar Interest b. Calculate the 60-day rate on the loan. 200,000 Loan Amount Total Dollar Interest 60-Day Rate c. Assume that the loan is rolled over each 60 days throughout the year under identical conditions and terms. Calculate the effective annual rate of interest on the fixed-rate, 60-day First American note. 0.0000% 60-Day Rate Number of 60-Day Periods/Year Effective Annual Rate Chapter 16 M U LLIP 16 Start Protected View] - Excel HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW OTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit. it's safer to stay in Protected View Enable Editing fr B D Next, create a spreadsheet to calculate the following for the First Citizen loan: d. Calculate the initial interest rate. Prime Rate Premium Initial Rate, First 30-Day Rate 7.00% 1.50% e. Assuming that the prime rate immediately jumps to 7.5% and after 30 days it drops to 7.25%, calculate the interest rate for the first 30 days and the second 30 days of the loan. 0.00% 30 Initial Rate + Premium Number of Days Maturity (Years) First 30-Day Rate Initial Rate + Premium Last 30-Day Rate 1. Calculate the total dollar interest cost. non Chapter 16 mm Type here to search o e 9 w x] PROTECTED VIEW Be careful files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View Enable Editing fi AB f. Calculate the total dollar interest cost. Loan Amount First 30-Day Rate Last 30-Day Rate Total Interest Cost 200,000 0.0000% 0.0000% g. Calculate the 60-day rate of interest. 200,000 Loan Amount Total Interest Cost 60-Day Rate h. Assume that the loan is rolled over cach 60 days throughout the year under the same conditions and terms. Calculate the effective annual rate of interest. 60-Day Rate Periods in Year Effective Annual Rate 0.0000% 6.08333 Chapter 16 Type here to search Enable Editing FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW PROTECTED VIEW Be careful files from the Internet can contain viruses. Unless you need to edit it's safer to stay in Protected View 429 1x & fr i. Which loan would you choose, and why? because it has the lowest effective rate. Points 88 Requirements 1 In cell E31, by using cell references to the given data, calculate the total interest rate (prime interest rate plus the interest rate premium) 2 In cell E32, by using cell references to the given data, calculate the loan maturity in years. 3 In cell E33, by using cell references to the given data, calculate the total interest payment. 4 In cell E39, by using cell references to the given data, calculate the 60-day interest rate. 5 In cell E44, by using cell references to the given data, calculate the number of 60-day periods in a year. 6 In cell E45, by using cell references to the given data, calculate the effective annual interest rate. 7 In cell E52, by using cell references to the given data, calculate the initial interest rate (first 30-day interest rate) 8 In cell E58, by using cell references to the given data, calculate the maturity in years. 9 In cell E59, by using cell references to the given data, calculate the first 30-day interest rate. 10 In cell E61, by using cell references to the given data, calculate the interest rate corresponding to the initial interest rate plus the premium rate. 29 11 In cell E62, by using cell references to the given data, calculate the last 30 day interest rate. 00 12 In cell E69. by using cell references to the given data, calculate the total interest cost. Chapter 16 Type here to search Milios Zutter Spreadsheet Chapter 16 Start (Protected View] - Excel LE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit. it's safer to stay in Protected View Enable Editing A D - X fo B H 8 In cell E58, by using cell references to the given data, calculate the maturity in years. 9 In cell E59, by using cell references to the given data, calculate the first 30-day interest rate. 10 In cell E61, by using cell references to the given data, calculate the interest rate corresponding to the initial interest rate plus the premium rate. 11 In cell E62, by using cell references to the given data, calculate the last 30-day interest rate. 12 In cell E69, by using cell references to the given data, calculate the total interest cost. 13 In cell E75, by using cell references to the given data, calculate the 60-day interest rate. 14 In cell E81, by using cell references to the given data, calculate the effective annual interest rate. 15 In cell C85, type either First Citizen or First American depending on which loan choice is better. 16 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. Chapter 16