When you evaluate a Treasury bill, or T-bill, you should determine the fair market value of the investment. In Excel, you can use the TBILLPRICE function to find that value. If you evaluate a T-bill and your formula displays a NUM error, you should check your settlement date and maturity date. T-bills have a life of one year or less, so you might have entered a date incorrectly.
- [Instructor] When you evaluate a treasury bill, or a T-bill, you should determine the fair market value of the investment. In Excel you can use the T-bill price function to find that value. I'll demonstrate how to perform this calculation using the TBillPrice_05_08 workbook. You can find it in the Chapter 5 folder of your Exercise Files collection. In this case, I have a T-bill that has the following characteristics. The first is the settlement date, and that is the date that you take possession of the investment.
Next is the maturity date, that is the date that the T-bill is paid off. One thing to note is that a bill has a duration of at most one year, where a bond has a duration of more than one year. So you see here that the duration is from August 1st of 2016 to December 31st of the same year. If the maturity date were to be August 2nd of 2017 or after, then the formula would return an error.
Finally there is the discount rate. Discount rate is the amount of risk-free return from an investment. So the question is how much would you be willing to pay for this T-bill? We can calculate that using the T-bill price function. So in cell C7, I'll type equal, then T-bill price, followed by left parentheses, and then our three arguments. Settlement date's in C3, comma, maturity dates in C4, comma, and discount rate is in C5.
Now when I type a right parentheses and press Enter, I see that based on $100 of face value, I would be willing to pay $98 and 10 cents, so that's the amount that the investment would accumulate over the course of the investment from the settlement date to the maturity date. And just to show how the investment might change, I will increase the discount rate from 4.50% to 5.25% and press Enter. And now I see that because of the higher interest, the higher discount rate, that I would be willing to pay a little bit less.
So the discount rate is what I can make from a risk-free investment or another investment, which means that because I can earn more elsewhere, I'm willing to pay a little bit less for this investment per $100.
Lynda.com is a PMI Registered Education Provider. This course qualifies for professional development units (PDUs). To view the activity and PDU details for this course, click here.
The PMI Registered Education Provider logo is a registered mark of the Project Management Institute, Inc.
- Recall what the type argument is used to determine when using the PMT function.
- Identify what the M stands for in the ACCRINTM function.
- Name the accounting rules used by the AMORDEGRC function to assign a depreciation coefficient to an asset.
- Recall what internal rate of return generated by the IRR function should be measured against to determine if it is a good investment.
- List the three regular intervals that coupon bonds pay interest at.
- Determine the function that provides a more conservative bond evaluation compared to the DURATION function.
- Explain what the RECEIVED function shows.
Skill Level Intermediate
1. Analyzing Loans, Payments, and Interest
2. Calculating Depreciation
3. Determining Values and Rates of Return
4. Calculating Bond Coupon Dates and Security Durations
5. Calculating Security Prices and Yields
6. Analyzing Simulation Results
Next steps1m 12s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.