PLEASE WRITE THE FORMULAS IN THE ANSWER!!!!!!
Financial Worksheet 5. Your business needs approximately $1,000,000 to purchase a piece of equipment to use in the production facility to produce a new product. Your task is to analyze several possible financing options. The options being considered to raise the funds for the equipment are as follows: Funding the project with a loan from Jayhawk Bank. Jayhawk Bank currently charges a fixed rate of 8% annual interest compounded quarterly. Payments are scheduled quarterly over five years. Funding the project by cashing in a money market account that was set up two years ago as an emergency fund. The fund started with an initial deposit of $900,000 and paid 3.5% annual interest compounded monthly. Excel 5 IST 310 Spreadsheet and Database Applications Page 1 Funding the project from an initial investment and current profits. This option requires the project to be delayed for a year and a half. A portion of the company's expected profits ($50,000 per month each month) would be during delay, and ash outl need to be determined. A money market account will be used to hold these funds. The current money market rates pay 4% annual interest compounded monthly Funding the project with a loan from Kansas Bank. The loan will be paid back over the next four years, with equal semiannual payments (compounded semiannually) of $150,000. Funding the project with a loan from Rock Chalk Bank. The loan would have a fixed interest rate of 6.5% per year compounded quarterly, and fixed quarterly payments of $95,000. Use various financial functions to determine the missing piece of information for each of the five options (PMT for cell E3, FV for cell G4, PV for cell F5, RATE for cell Co, and NPER for cell D7). When using Excel financial functions, pay close attention to the e compounding period being used. The financial functions apply the interest rate per period and the payment per period to the principal over a specified number of It does not matter if the compounding period is months, days, quarters, years, or some other specified period. A financial function applies the appropriate rate and payments for the specified number of times. If the rate and number of period arguments and the payment are not all consistent with the compounding period duration, the wrong values will be calculated. The payment on a loan of 8% per year for five years compounded once per year is different from the payment on that same loan amount compounded quarterly with a rate of 2% per quarter (8% divided by 4) over 20 quarters (5 years multiplied by 4 quarters). For the loan being by Jayhawk Bank, the interest rate per year is 8% over a period of five years. Because the compounding period is quarterly, a rate of 2% per quarter is applied over 20 separate periods. The value that you are calculating with the PMT function is the payment per quarter. Your completed analysis should look like the screenshot on the top of the next page. periods. offered Excel 5 IST 310: Spreadsheet and Database Applications Page 2 Option 3 Jayhawk Bank loan 4 Emergency Fund 5 Delay Project and Use Profits 6 Kansas Bank Loan 7 Rock Chalk Bank Loan Project Financing Options Periods Per Year Annual Interest Rate Duration in Years Periodic Payment Present Value Future Value 1561,157) $1.000.000 3.5% 2.00 $0 $900,000 $365,159 12 4.0% $50,0001) ($69,736) $1,000,000 4.00 $150,000) $1,000,000 6.5% 2.91 1595,000 $1,000,000 A1 X V fx Project Financing Options A B C D E F G H I Periods Per Year Periodic Payment Future Value $0 4 1 2 Option 3 Jayhawk Bank Loan 4 Emergency Fund 5 Delay Project and Use Profits 6 Kansas Bank Loan 7 Rock Chalk Bank Loan Present Value $1,000,000 ($900,000) Project Financing Options Annual Interest Rate Duration in Years 8.0% 5.00 3.5% 2.00 4.0% 1.50 4.00 6.5% 12 12 $0 ($50,000) ($150,000) ($95,000) 2 $1,000,000 $1,000,000 $1,000,000 $0 $0 4 8 9 10 Financial Worksheet 5. Your business needs approximately $1,000,000 to purchase a piece of equipment to use in the production facility to produce a new product. Your task is to analyze several possible financing options. The options being considered to raise the funds for the equipment are as follows: Funding the project with a loan from Jayhawk Bank. Jayhawk Bank currently charges a fixed rate of 8% annual interest compounded quarterly. Payments are scheduled quarterly over five years. Funding the project by cashing in a money market account that was set up two years ago as an emergency fund. The fund started with an initial deposit of $900,000 and paid 3.5% annual interest compounded monthly. Excel 5 IST 310 Spreadsheet and Database Applications Page 1 Funding the project from an initial investment and current profits. This option requires the project to be delayed for a year and a half. A portion of the company's expected profits ($50,000 per month each month) would be during delay, and ash outl need to be determined. A money market account will be used to hold these funds. The current money market rates pay 4% annual interest compounded monthly Funding the project with a loan from Kansas Bank. The loan will be paid back over the next four years, with equal semiannual payments (compounded semiannually) of $150,000. Funding the project with a loan from Rock Chalk Bank. The loan would have a fixed interest rate of 6.5% per year compounded quarterly, and fixed quarterly payments of $95,000. Use various financial functions to determine the missing piece of information for each of the five options (PMT for cell E3, FV for cell G4, PV for cell F5, RATE for cell Co, and NPER for cell D7). When using Excel financial functions, pay close attention to the e compounding period being used. The financial functions apply the interest rate per period and the payment per period to the principal over a specified number of It does not matter if the compounding period is months, days, quarters, years, or some other specified period. A financial function applies the appropriate rate and payments for the specified number of times. If the rate and number of period arguments and the payment are not all consistent with the compounding period duration, the wrong values will be calculated. The payment on a loan of 8% per year for five years compounded once per year is different from the payment on that same loan amount compounded quarterly with a rate of 2% per quarter (8% divided by 4) over 20 quarters (5 years multiplied by 4 quarters). For the loan being by Jayhawk Bank, the interest rate per year is 8% over a period of five years. Because the compounding period is quarterly, a rate of 2% per quarter is applied over 20 separate periods. The value that you are calculating with the PMT function is the payment per quarter. Your completed analysis should look like the screenshot on the top of the next page. periods. offered Excel 5 IST 310: Spreadsheet and Database Applications Page 2 Option 3 Jayhawk Bank loan 4 Emergency Fund 5 Delay Project and Use Profits 6 Kansas Bank Loan 7 Rock Chalk Bank Loan Project Financing Options Periods Per Year Annual Interest Rate Duration in Years Periodic Payment Present Value Future Value 1561,157) $1.000.000 3.5% 2.00 $0 $900,000 $365,159 12 4.0% $50,0001) ($69,736) $1,000,000 4.00 $150,000) $1,000,000 6.5% 2.91 1595,000 $1,000,000 A1 X V fx Project Financing Options A B C D E F G H I Periods Per Year Periodic Payment Future Value $0 4 1 2 Option 3 Jayhawk Bank Loan 4 Emergency Fund 5 Delay Project and Use Profits 6 Kansas Bank Loan 7 Rock Chalk Bank Loan Present Value $1,000,000 ($900,000) Project Financing Options Annual Interest Rate Duration in Years 8.0% 5.00 3.5% 2.00 4.0% 1.50 4.00 6.5% 12 12 $0 ($50,000) ($150,000) ($95,000) 2 $1,000,000 $1,000,000 $1,000,000 $0 $0 4 8 9 10
