September 25, 2007
Spreadsheet Mini Project
Objective: This assignment will be used in Business Foundations during the unit discussing financial planning. The students in this class will not have the skills to create this spreadsheet on their own. The spreadsheets have been protected so that the students can only manipulate data in the loan payment calculator portion. I will use this model to explain the different formulas and functions used, so that students understand the concepts behind the spreadsheet. Students will use an Excel loan payment calculator to manipulate data that includes an interest rate schedule, amortization schedule, hyperlinks to financial institutions, a chart that visual depicts yearly interest payments versus amount paid on principal, and a vlookup function that determines poor, average, good interest rates. Students will use this spreadsheet to determine the financial cost of credit when purchasing items using a loan or credit card. This worksheet is set up so that students can manipulate the key factors to calculate interest in different types of consumer credit situations such as student loans, home mortgages, or credit card purchases.
Assignment: Using credit to purchase goods and services may allow you to be more efficient or more productive. Valid reasons exist for using credit. A medical emergency may cause financial need. You may be offered a new job and need a car to get to work. There are many choices about what and where to borrow. Wise credit shopping involves careful analysis of the cost of credit. If you are thinking of borrowing money or obtaining a credit card, consider two factors. First, figure out how much it will cost you. Second, determine whether you can afford it. After deciding to use credit, you should shop around for the best terms. Consumer credit enables you to have and enjoy goods and services not and to pay for them with future income. Always remember that credit is not free.
This assignment will allow you to see monthly payments, total interest, and total cost on the basis of loan data entered. The interest rate schedule will update the monthly payment, total interest, and total cost for a range of interest rates based on the data entered. The amortization schedule will summarize loan information over the life of the loan. There are two different worksheets for this assignment. The short term worksheet will give you information regarding loans that have ten years or less to pay back. The long term worksheet will give you information regarding loans that have more than ten years to pay back. The charts corresponds to either short term or long term calculations.
Click here for a screen shot of the Excel spreadsheet.
Click here for a screen shot of the Excel chart.
Enter the following information into the Loan Payment Calculator/Long Term:
- Item-House
- Price-$198,000.00
- Down Payment-$25,000.00
- Rate-6.25%
- Years-15
Now that you have entered the data, make the following changes:
- Down Payment-$15,000.00
- Rate-7.25%
- Years-30
Enter the following information into the Loan Payment Calculator/ShortTerm:
- Item-24' Boat
- Price-$32,550.00
- Down Payment-$5,000.00
- Rate-7.75%
- Years-5
Now that you have entered the data, make the following changes:
- Down Payment-$10,000.00
- Rate-6.55%
If you click on the graphic, you will be able to look at interest rates that are currently available.
The chart worksheet plots yearly interest payment versus paid on principal amounts. You will be able to visualize the effect of time increasing the amount of interest owed when using credit.
Finally, you will be able to determine if the rate of interest you are using in the loan calculator is catagorized as low (good credit), average, or high (bad credit).
Posted by ascummings at September 25, 2007 09:28 AM
Trackback Pings
TrackBack URL for this entry:
http://www.bsu.edu/eidm/journal/mt/mt-tb.cgi/732