Assignment 3: Rate Calculations
Submit the two parts of this assignment in a spreadsheet. It is acceptable to put both parts on one tab or put them in separate tabs.
Grading for Assignment 3 will be performed by substituting several numbers for the boxes above each table. So you can insert any value you wish in the boxes at the top of the table o determine if your table works correctly.
Note that if you are using Excel 2003, the Nominal and Effect functions may not be available to you. The equivalent equations are in this week's content. The use of either functions or equations is acceptable.
Part 1:
Create a spreadsheet table that shows for a particular Compounding Frequency and Nominal Annual Rates (NAR) for 10% to 3%, the corresponding Periodic Interest Rate (PIR) and Effective Annual Rate (EAR). Use the format as shown below. A manual change in the Compounding Frequency in the box above the table should cause the PIR and EAR columns in the table to automatically change for all of the NARs of 10% to 3%. Do this by entering the appropriate equations in the PIR and EAR columns. Format the PIRs and EARs as percentages with two decimal places (xx.xx%)
Compounding Frequency ? Times Per year
NAR PIR EAR
10%
9%
8%
7%
6%
5%
4%
3%
Part 2:
Create a second spreadsheet table that shows the monthly car payments for a particular loan amount and length of the loan in years. That is, the resulting table should enable one to type in any loan amount and length of the loan (examples are shown in boxes at the top of the table), and the monthly payments will automatically be shown for the NARs of 3% to 8% that have monthly compounding. Format the monthly payments as dollars with 2 decimal places ($xx.xx)
Loan Amount $20,000
Length 6 years
NAR Monthly Payment
3%
4%
5%
6%
7%
8%