Question

. 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. • 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 invested during the delay, and an initial cash outlay would need to be determined. A money market account will be used to hold Project 2 IST 310: Spreadsheet and Database Applications Page 5 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 C6, and NPER for cell D7). When using Excel financial functions, pay close attention to the 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 periods. 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 offered 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 this:

Option	Periods Per Year	Annual Interest Rate	Duration in Years	Periodic Payment	Present Value	Future Value
Jayhawk Bank Loan	4	8.0%	5.00		$1,000,000	$0
Emergency Fund	12	3.5%	2.00	$0	($900,000)
Delay Project and Use Profits	12	4.0%	1.50	($50,000)		$1,000,000
Kansas Bank Loan	2		4.00	($150,000)	$1,000,000	$0
Rock Chalk Bank Loan	4	6.5%		($95,000)	$1,000,000	$0

Answer 1

The basic formula to be used is: rate = annual interest rate/periods per year; NPER = Duration in years*periods per year

Formulas: