Time value of money and financial calculator tutorials
Tutorials by Brand:
Time value of money and financial calculator tutorials
The article analyzes bond payment frequencies for S&P 500 companies to verify if semiannual payments are universally used. Using a dataset of 15,465 bonds, the study finds that 93.5% pay semiannually, justifying textbook assumptions. However, the Finance sector issues most of the exceptions, including monthly and other frequencies. Most sectors only use semiannual payments, supporting the emphasis in academic resources.
Random numbers drawn from a particular probability distribution (i.e., random variates) are frequently needed in many fields, particularly for simulations. There are many algorithms for generating random variates, but I will focus on one simple technique that can be used with some built-in Excel functions. This technique is known as the Inverse Transform Method. For our purposes, we want to…
In the original amortization schedule tutorial I left out a feature that is of interest to a lot of people: adding extra principal payments in order to pay off the loan earlier than the loan contract calls for. In this tutorial we will add this feature. Before we get started let me mention one important thing: You can almost always…
Typically, in finance textbooks, bond valuation is discussed with a hidden assumption: that you are valuing the bond on a coupon payment date. If that is the case, then you can use the PV function to find the price (though there is another hidden assumption in there: that the yield curve is flat). The problem with this is that usually…
This is the first of a two-part tutorial on amortization schedules. In this tutorial we will see how to create an amortization schedule for a fixed-rate loan using Microsoft Excel and other spreadsheets (the next part shows how to handle extra principal payments and also includes a sample spreadsheet using this same example data). Almost all of this tutorial also…
Virtually every finance textbook has at the back, a series of tables that contain multipliers that can be used to easily calculate present or future values without the need for a financial calculator. In recent years these tables have given way to financial calculators, but they are still used by some professors and on some professional exams. This tutorial will…
The article demonstrates randomness in stock price movements using an Excel simulation inspired by A Random Walk Down Wall Street by Burton Malkiel. It starts with a simple model based on coin tosses, where a "head" causes the price to rise and a "tail" causes it to fall, mimicking the unpredictability of markets. The article then uses geometric Brownian motion for more realistic modeling. Excel functions and VBA are employed to generate data and create high-low-close stock charts, showing that many technical patterns can emerge purely from random processes.
The article explains that Excel's NPV function does not truly calculate Net Present Value because it doesn’t include the initial investment. Instead, it calculates the present value of uneven cash flows. To get real NPV, subtract the initial outlay from the NPV result or include it in the cash flow range and adjust the time period. It also introduces a third method using the PV function with arrays to achieve the same result in one step.
Duration and convexity are important numbers in bond portfolio management, and duration is pretty simple in Excel because there are built-in functions. Convexity, though, is another matter. Of course, there are formulas that you can type in (see table below), but they aren’t easy for most people to remember and are tedious to enter. In this article I will show…
One of the key variables in choosing any investment is the expected rate of return. We try to find assets that have the best combination of risk and return. In this section we will see how to calculate the rate of return on a bond investment. If you are comfortable using the built-in time value functions, then this will be…
Strictly speaking, an annuity is a series of equal cash flows, equally spaced in time. However, a graduated annuity is one in which the cash flows are not all the same, instead they are growing at a constant rate. So, the two types of cash flows differ only in the growth rate of the cash flows. Annuity cash flows grow…
TVMCalcs’ Excel Tutorial Index This page contains a list of Excel tutorials for financial applications. I have posted the following tutorials: If you have any requests for content, please feel free to send me an email.
In recent years, bond issuers have changed from the traditional call schedule to a “make-whole” type of call. Generally, this is good for investors as it makes it less likely that high interest bonds will be called. If it is called, then they are “made whole” because they are paid the present value of the remaining cash flows. In a…
A bond is a debt instrument, usually tradable, that represents a debt owed by the issuer to the owner of the bond. Most commonly, bonds are promises to pay a fixed rate of interest for a number of years, and then to repay the principal on the maturity date. In the U.S. bonds typically pay interest every six months (semi-annually),…
In the previous section we looked at using the basic time value of money functions to calculate present and future value of annuities (even cash flows). In this section we will take a look at how to use Excel to calculate the present and future values of uneven cash flow streams. We will also see how to calculate net present…