Home Work Assignment 2

for MSIS 220 sections 10 and 20

Last modified on 

Due Nov 11 1998 in class

Note:

You are to hand in all three projects in a single floppy disk. Make sure to 

  • put your name, student ID number and the date on the floppy disk label
  • The main input file for this assignment is in the web site http://karush.rutgers.edu/CLASSES/98fallMIS220/Homeworks/hw3dat1.xls
  • Important: Adhere to the practice of not using constants inside formulas. Instead put the constants somewhere in the worksheets and reference them in the formula. You will be graded on this!


Overview

A small company has certain amount of budget to buy computers and vans. Specifically the company needs 10 computers and three vans. There are a number of ways to finance the purchase of computers and vans. Here are is the list:

  1. Pay cash up front.
  2. Lease by paying some amount up front, and a monthly lease for three years. At the end if the company wants to keep the computer (or van) then the balance should be paid then.
  3. 3 Get a fixed rate loan from a bank and pay off the loan in three years
  4. Get an adjustable rate loan from the bank and pay it off in three years. For adjustable loans assume that adjustments are made at the end of the first year, and at the end of second year. Rates are tied to the prime rate charged by the Federal Reserve board, the FED. Also the initial interest rate is tied the same way to the prime rate.

In this assignment you are going to look at various scenarios for interest rates and make a decision as to which option is the best.

General comment 1: In order to compare two or more monthly payments over the same period of time, a device named "present value analysis" may be used. The idea is simple. We all agree that if you have a choice to own $100 now as opposed to $100 in a year, we choose to have it now. In other words the $100 in a year is less valuable than the $100 now. But what is its value now? $95? $90? To determine that economists use "discount rate" which is essentially the same as interest rates. If discount during the next year is say 15%, then today’s $100 will be worth 100´ (1+0.15)=$115. Or said it conversely, "the present value of $115 in a year is $100". Thus the present value of $100 in a year is 100¸ (1+0.15)=$86.96.


Project 1

Open Excel and from the web site: http://karush.rutgers.edu/CLASSES/98fallMIS220/Homeworks/hw3dat1.xls obtain the raw data and import it to your workbook. Call the workbook "rawdata". As you can see this workbook contains the following data about computers and vans and other prices and rates:

  • The price of computers
  • The number of computers needed
  • The price of vans
  • The number of vans needed
  • The fixed interest rate the bank charges for three year loans for each level of borrowing (thus the rates may change if much larger amounts are borrowed)
  • The current prime rate as announced by the FED.
  • The premium over the prime rate to get the adjustable rate. (Thus if the prime rate is 8% and the premium is 10% then the adjustable rate will be 18%.)
  • Three scenarios for the possible scenarios prime rate at the end of the first and second years
  • The monthly payment required if the company chooses to lease the computers
  • The final payment if the company wants to keep the computers after three years
  • The monthly payment required if the company chooses to lease the vans
  • The final payment if the company wants to keep the vans after three years

Create a separate workbook for each possible outcome:

  1. One for the cash payment
  2. One for taking the lease
  3. One for taking loan with fixed rate
  4. Three for the variable rate loan, one for each possible future prime rate.

In each work book drill the following information:

  1. Make a title column. Assume payments start form Nov 98 and continue for three years (i.e. through Oct 01) Also add at the top a row for initial loan
  2. Make a column titled "monthly payment"
  3. Make a column for "balance remaining"
  4. A few columns to the right of the last column make a column for "present value" calculations.

Now first fill out the Cash worksheet. The present value of the cash is itself. Thus if you pay $4000 upfornt the present value is $4000.


Project 2

Now Fill out the monthly payment for the fixed rate loan.

Here is how you calculate the monthly payment. We have the following data:

  • C = Initial Loan Amount
  • R = Th annual rate of interest
  • T = Number of years
  • N = Number of periods in a year (e.g. 12 if period is monthly)
  • p = Periodic payment (monthly payment if period is month.)

First if the annual interest rate is R, then the periodic interest rate is r=R/N. Suppose you borrowed C dollars. Then, at the end of the first period you owe C(1+r) and you pay a payment of p (which is to be determined.) Thus you owe only C(1+r)-p. At the end of the second period you owe

[C(1+r)-p](1+r)-p=C(1+r)2-p(1+r)-p

after you pay another p. At the end of the third period you owe

  1. [C(1+r)2-p(1+r)-p](1+r)-p=C(1+r)3-p(1+r)2-p(1+r)-p
  2. Continuing this pattern we see that at the end of the kth period you will owe

    C(1+r)k -p[(1+r)k-1+…+1]=C(1+r)k-p[(1+r)k--1]/r

    At the very end TN has passed and at that time you should owe zero:

    C(1+r)NT-p[(1+r)NT-1]/r=0

    Solving this for p we get:

  3. p= Cr(1+r)NT/[(1+r)NT-1]

Use formulas i. and ii. And fill out the columns for "monthly payment" (which is fixed throughout) and "Balance remaining".

For fixed rate worksheet add two additional columns called "Interest this month" and fill it out. Notice that at the beginning of the kth month, since you owe

C(1+r)k-1-p[(1+r)k-1--1]/r

The interest payment is r times this amount.


Project 3

To be given by soon. Stay tuned …..

Project 4

To be given by soon. Stay tuned …..

Project 5

To be given by soon. Stay tuned …..

Return to: Homework page | MSIS 220 page