π Spreadsheets for bond price and YTM problems
Spreadsheets and financial calculators can be very helpful with bond pricing and yield to maturity problems. Free online versions of both Excel and Google Sheets are available (I find Google Sheets to be more reliable), and I have a 30 minute tutorial on how to use them below.
If you already are familiar with spreadsheets, there are two functions that are most helpful for bonds:
- PV() is used to calculate bond prices
- RATE() is used to calculate YTM
If you already know how to use NPV() and IRR() from e2000, you can use those instead - theyβll work fine, too. In general, the functions you can use for bonds come in pairs:
- You can solve any bond problem using either PV() or NPV(). NPV() takes a little more time to use, but it can also be used for general NPV questions (not just for bond pricing).
- You can solve any YTM problem either using RATE() of IRR(). IRR() takes a little more time to use, but can be used for general IRR() questions (not just YTM questions). You can also use the YIELD() and PRICE() functions to calculate a bondβs yield and price, but these functions require you to enter specific dates, so I donβt recommend them.
An introduction to spreadsheets
Iβve made a quick tutorial for learning the key skills that I find most helpful when doing work for the classes I do with Bruce. I estimate it should take 20-30 minutes total, and after you go through it, you will learn more about Excel as you watch sections.
Hereβs the link
Student feedback: Iβve been through your spreadsheeting tips and it was good! Iβm not yet as serene as the guy working the laptop in the Microsoft clips but I will get there.
Introducing PV(), NPV(), RATE(), and IRR()
For bond pricing and yield questions
There are two additional tabs that includes examples of how to calculate bond price and yield when the coupons are paid semiannually. While the topic is interesting, you are only responsible for what Bruce covers in lecture.
Many more examples
