CIS200 – Homework #1 – Simple Formulas & Functions



CSE101 – Homework #5 (10 points) – Financial Functions

[pic]

Above is a spreadsheet analyzing several possible mortgage scenarios to finance your new home. The purchase price of this home is given in cell B1. You have compiled a list of possible lenders to finance your home and entered the terms of the mortgages they offer on an Excel spreadsheet. This information is given in the non-highlighted cells including the bank name, annual interest rate, required down payment, loan duration in years and number of compounding periods per year. Each bank will finance the purchase price of this home less the required down payment. For example, 5th Third bank will finance 95% of the purchase price (price minus 5% down payment) at 6.9% annual interest compounded monthly (12 periods per year) over a 30-year period. Complete the analysis by answering the following questions.

Please note when writing formulas, to receive full credit you must use correct Excel syntax (ie:use * for multiplication, / for division, etc.). Do not use unnecessary $ or functions. Also note, when writing your formulas, use cell references whenever possible.

1. (1/2 point) Write an Excel formula in cell F4, which can be copied down the column into cells F5:F8 to determine the face value of this loan, the selling price of this house less the down payment.

2. (1 points) Write a formula for cell G4, which can be copied down the column into cells G5:G7, to determine the interest that will be owed for the first period of the loan rounded to the nearest dollar.

3. (1 points) Write a formula for cell H4, which can be copied down into cells H5:H7, to determine the payment per period on this loan.

4. (1 points) An alternate bank has verbally described a 30-year loan with 5% down payment. This loan would require quarterly payments (compounded quarterly) of $2400 (see row 8). Write a formula in cell B8 to determine the annual interest rate they are charging?

5. (2 points) You need to determine if you have sufficient funds for the down payment. You will be funding the down payment from a zero coupon bond that you purchased for $5000 ten years ago. This bond accrued interest at 6% compounded annually. Write a formula in cell I4, which can be copied down into cells I5:I7, to determine (True/False) if you have sufficient funds to cover the down payment for this loan. (Hint: Calculate the value of the bond now and compare that value to the required down payment).

6. (1 points) Write a formula in cell G10 to determine the number of years it would take to save for the down payment on the National City mortgage if you were to invest $100 per month at 5¼ % annual interest compounded monthly?

7. (1 points) Part of the money you planned on spending for a down payment is urgently needed toward a new car and to payoff some medical bills. You’ve considered putting off the purchase of this house for 3 more years at which time you’d like to have a $12,000 resulting from investing part of the funds you have now and an additional $50 per month in savings. Write a formula in cell G11 to determine how much you would need to invest now if you plan on having a nest egg of $12,000 in 3 years assuming additional investments of $50/month. Assume you will receive an annual interest rate of 3% compounded monthly.

8. (1 point) Write a formula for cell J4, which can be copied down into cells J5:J7, to determine if you should consider this loan or reject it. You will only consider loans that have periodic payments of less than $800. Remember your payments were calculated at negative numbers, so you will need to that into consideration in your formula!

9. (1 point) BankOne has offered you an alternate to their original loan for exactly the same down payment and rate terms but allowing you to make a Balloon payment of $20,000 payable at the end of the loan duration. This should help reduce your monthly payments. Write an Excel formula in cell G12 to calculate this new monthly payment.

10. (1/2 point) The last argument of a financial function is type, which defaults to 0 if omitted. What is the difference between a type 0 and a type 1 argument in a financial function?

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download