Effective Annual Rates (APR resulting EAR)

2 parts- question

Part-1
Create a table using Excel that shows for Annual Percentage Rates (APRs) for 15% to 1%, the corresponding rates per period (period rate = APR/ number of periods per year), and the resulting Effective Annual Rates. The Periods per year, Period Rates and EARs should automatically change when the length of the period is changed.

This should be accomplished using the two cells above the table shown below. The first one (manually entered) is the length of the period in months (period length). The second, below this, is the number of periods per year which should be calculated by dividing 12 by the period length. For instance if the period length is 2 months, the number of periods per year is calculated as 12/2=6. The equations inserted in the table cells should use this "periods per year" so that the table is updated automatically when the "periods per year" is changed.

Therefore, a manual change in the Period length in the box above the table should cause 1) the Periods per year, and 2) the other cells in the table to automatically changed for all of the APRs of 15% to 1%.

Part 2:

Create a second table using Excel that shows the Periods per Year, Period Rate and EAR for six period lengths (1,2,3,4,6,and 12 months). These should be dependent on an APR in the box at the top of the table. This format is shown below. A change in the APR in the box above the table should automatically change the values in the table.

Therefore, using the APR and equations, calculate the Periods per year (=12/Period length), Period Rate (using APR and Periods per year), and the EAR .