TVMCalcs’ Excel Tutorial Index

This page contains a list of Excel tutorials for financial applications.

I have posted the following tutorials:

• Microsoft Excel as a Financial Calculator. It covers the basics of using the FV, PV, Rate, NPer, and PMT functions just like you would otherwise use on your financial calculator.
• Excel’s NPV Function Doesn’t Calculate Net Present Value. Did you know that Excel’s NPV function doesn’t really calculate the Net Present Value? It doesn’t, and this post explains why and how to calculate it.
• How to Calculate Duration and Convexity with Excel. There are built-in functions for modified duration and Macaulay duration, but not for convexity. Learn how to calculate all of these using other methods and the built-in functions.
• Graduated (or Growing) Annuities – A graduated annuity, also called a growing annuity, is like a normal annuity, except that the payment grows at a constant rate. In this tutorial I show you how to find the present value and future value of a graduated annuity.
• How to Create a Loan Amortization Schedule. It also shows how to use conditional formatting.
• How to Allow for Extra Principal Payments on the Amortization Schedule. This is part two of the amortization schedule.
• Bond Valuation. This covers the methodology of valuing bonds on, and between, coupon payment dates.
• Accrued Interest. Three ways to correctly calculate accrued interest on a bond using the right day count convention.
• Calculating Bond Yields. This covers the calculation of the current yield, yield to maturity, and yield to call both on and between coupon payment dates.
• Creating Time Value of Money Tables. Have you used the PVIF, FVIF, PVIFA, and FVIFA tables at the back of a finance or accounting textbook? Most classes use financial calculators these days, but I often get requests from students for tables. In this tutorial I demonstrate how to create these tables using Excel’s conditional formatting, two-input data tables, data validation, and time value of money functions.
• Make-Whole Call Valuation for Corporate Bonds – Do you know how to calculate the call price for a callable bond? Most bonds these days use a type of call known as a “make-whole” call provision. This tutorial explains how to calculate the call price.
• Generate Random Variates – Do you need to generate random numbers from a particular probability distribution? If so, then my tutorial will show you a simple way to do this using only built-in Excel functions.
• US Corporate Bond Payment Frequencies – Curious if all corporate bonds really pay interest semiannually as textbooks seem to claim? We analyzed over 15,000 S&P 500 bonds and found some interesting results. Explore the details!

If you have any requests for content, please feel free to send me an email.