Group Activity 14 Loans

All group activities must include a statement signed by all members of your group that each group member fully participated in the activity. Save it to the desktop and save frequently during the hour.

Learning Goals for this Activity

1. You will be able to calculate loan payments using the PMT function in Excel.
2. You will be able to calculate the total amount paid, the total interest paid and the total principal paid on the loan.

To use the PMT Function...
Choose the Function Wizard button then Financial in the left column and PMT in the right column. A screen will appear and you will be prompted to enter values.

Rate is the loan interest rate. (Monthly amount)
Nper is the number of payments. (Total number of payments to be made)
Pv is the amount of the loan. (No change - just the amount of the loan)
Fv is the future value of the loan and is not filled in.(Leave blank)
Type is indicating whether the payment is made at the beginning or the end of the month. Use 0 for this problem.(Just enter 0 or leave blank)

Amortization Table

1. You have graduated from DePaul and decide to buy a condominium in Chicago for $239,000.   The one bedroom, one bathroom condo is located on St. James Place in Lincoln Park.  Assuming you need a loan for $227,050 what will your payments be assuming a 30 years mortgage at 6%?

Prime location in Lincoln Park. Fantastic mix of vintage and modern with a newer kitchen w/ granite, stainless steel & cabinets & counterspace galore! Updated bath (w/ whirlpool), walk-in closet in master, freshly & professionally painted. Surround system thruout. Built-in bookcases in large living room. Walk to lake, train, shopping & nightlife.

2.  What is the total amount you end up paying including principal and interest?

3.  For comparison purposes, you also look at a 15 year mortgage at 5.5% for 15 years.  What is your monthly payment?

4.  What is the total amount you end up paying including principal and interest if you decide on the 15 year mortgage?

5.  Which loan is better,  30 years at 6% or 15 years at 5.5%?  Explain.

6.  As we learned in class, the formula to calculate the amount of interest payable each month is computed as follows (where n = 12) :

In many cases, interest rates are converted to a daily rate, n = 365 and then multiplied by the number of days since the last payment (if it is the first payment, interest is calculated from the date of the loan) to determine the amount of interest due.  The updated formula is as follows: 

Now lets apply this to one of the latest loan gimmicks, Payday Loans.  My Cash Now offers Payday Loans for a loan fee (finance charge).  The loan fee is actually the interest paid on the loan.  Using the formula above, you can determine the APR knowing the loan fee, the loan amount and the loan term (in days).

a.  Let's assume that you need $100 (which is the balance) and you can't wait until your next paycheck.  You stop by My Cash Now and they tell you they can lend you $100 for 14 days and the loan fee (finance charge) is $18.62 (also considered the interest).  Using the formula above, calculate the APR.

b.  How does this rate compare to the other Annual Percentage Rates (APR) we have discussed and used in class?  (Be sure you converted you answer above into a percent.)