CIS200 – Homework #1 – Simple Formulas & Functions



Name _____________________________ Lecture Time ________________

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

[pic]

Loans!

[pic]

Bond!

Our workbook consists of two worksheets where some initial information on financing a car has already been laid out. Sheet Loans! Consists of several possible loan scenarios for the purchase of a this new car. The information in the non-highlighted areas is provided for you. For example, row four displays loan information from 5th/3rd bank. It assumes you will purchase the car for $35,000, with a down payment of $1000 (column B), thus the loan amount is $34,000 payable monthly (12 payments per year given in column E) over the next three years (loan duration in column D) with no balloon payment (Column H) at the end of the loan. You will be completing the rest of the information for each loan (each loan is displayed in a single row), and answering several additional questions as detailed below.

Worksheet Bond! contains information on a zero-coupon bond that you own – including the current redemption value of the bond, its annual interest rate and compounding periods. Use the values given here to answer questions 9 and 10.

Write the formulas such that if any of the inputs change (down payments, balloon payments, etc), the formulas will still work. 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 a formula in cell C4, which can be copied down to the cells C5:C8, to display the loan amount. Note that the down payments in column B were entered as negative values. Assume that this loan is compounded monthly.

2. (1 points) Write a formula for cell G4, which can be copied down into cell G5, to determine the annual interest rate of this loan. Assume that this loan is compounded monthly.

3. (1 points) National City Bank has offered you a loan at 5.5% interest rate with a down payment of $2500. They have told you that payments will be $900 per month. Write an Excel formula in cell D6 to calculate the duration of the National City loan in years. Assume that this loan is compounded monthly.

4. (1/2 point) How would you modify the formula in question 2 if the payments are to be made at the beginning of each period? Assume that this loan is compounded monthly.

5. (1 points) Ohio Savings & Loan has also approved you for a loan with 6.5% interest, no money down, and no balloon payments. Their loan is payable over the next four years with monthly payments. Write an Excel formula in cell F7 to determine the monthly payment that will be required on this loan. Assume that this loan is compounded monthly.

6. (1 points) Citibank is offering a loan to be paid quarterly over the next 5 years requiring $1500 per quarter and no money down. Whatever interest/principal is unpaid at the end of the 5 years will be due as a balloon payment. Given an annual interest rate of 6%, write an Excel formula in cell H8 to determine the value of the balloon payment. Assume that this loan is compounded quarterly.

7. (1 points) Write a formula in cell I4 to determine the total interest that you pay over the life of this loan (hint: value of all payments less the face value of the loan). Write the formula so it can be copied down the column. Pay attention to the sign of the values. The total interest you pay should be displayed as a negative value.

8. (2 points) You’re in a short-term financial crunch and want to accept a loan which minimizes your monthly payments. However, you aren’t considering the Citibank loan since you don’t want such a lengthy commitment. Given these criteria, write a formula in cell J4 to determine whether or not to accept this loan. Write the formula so it can be copied down the column into cells J5:J7. The formula should return either “select” or “reject”.

9. (1 points) You have just realized that you still own a bond which is coming due this week. The redemption value (amount of money that it is worth now) is $40,000. It has been accruing (earning) interest at 6% per year compounded monthly for the past ten years. No additional contributions or withdrawals were made. What was the original bond investment that was made ten years ago?

10. (1 points) The government charges a 15% capital gains tax on the earned interest (redemption value less original value). Write a formula in cell Bonds!D8 to determine (True/False) if you have sufficient funds in which to purchase this vehicle from the proceeds of this bond after taxes are paid.

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

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

Google Online Preview   Download