Rate Calculations

Compounding Frequency and Annual Percentage Rates

Assignment 3: Rate Calculations

This assignment has three parts.  Submit them in a spreadsheet.

Part 1:

Create a spreadsheet table that shows for a particular Compounding Frequency and Annual Percentage Rates (APRs) for 12% to 1%, 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 APRs of 12% to 1%.

Part 2:

Create a second spreadsheet table that shows for a particular APR and the Compounding Frequencies shown in the table below, the  PIR and EAR.  Use the format as shown below.  A change in the APR in the box above the table should automatically change the PIR and EAR values in the table below it.

Part 3:

Create a third spreadsheet table that shows for a particular EAR and the Compounding Frequencies  in the table, the corresponding PIRs and APRs.  Use the format as shown below.  A change in the EAR in the box above the table should automatically change the PIR and APR values in the table below it.

Grading for Assignment 3 will be performed by substituting several numbers for the boxes above each table.  Therefore the values that you submit for Compounding Frequency in Part 1, APR in Part 2, and the EAR Part 3 can be any value you choose.  If you want, enter compounding frequency of 2 for part 1, APR of 6% for part 2 and EAR of 8% for part 3, but understand that the three tables should automatically update for any values.

The primary purpose of this assignment is to gain insight into the relationships between the number of compounding periods, PIRs, APRs and EARs.

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.  Either functions or equations are acceptable.

Assignment 4: Bonds and Stocks

An investment fund is considering two long term investments.  Which investment offers the best rate of return assuming equal risks and a 10 year investment?

Bond:  Coupon bond with a face value of $100,000 that can be purchased today for $70,000  that matures in 10 years.  Its annual coupon rate is  8% paid semi-annually.

Stock:  A stock whose shares can be purchased for $84 per share today and its price is forecast to grow 12% annually for the next 10 years.  It will pay dividends of $1.50 semi-annually.  Note that the future price of the stock can be calculated using the annual growth rate and the FV function.

Briefly discuss your recommendation.  Submit using a spreadsheet.