The remaining time to maturity is irrelevant if the available interest rate remains unchanged. Assume that the bond now has five years (10 semiannual periods) left to maturity. The NPV is still $1,000 because, even though fewer coupon payments remain, the principal is closer to repayment, so it is discounted by (1+0.015) to the 10th power rather than the 18th power.

SVB invested in 10-year Treasury notes when the interest rates were even lower; let’s say 1.5% as an example. I saw a 3.5% rate quoted around March 2023, so let’s see what happens if there were eight years left to maturity for SVB’s notes. Also assume 3.5% applies to eight-year notes. There are 16 semiannual periods left, the required semiannual rate of return is 1.75%, and the coupon payment is $7.50. The results aren’t pretty. =*PV*(0.0175,16,–7.5,–1000) returns $861.50, i.e., if the bonds must be sold immediately, each will incur a $148.50 (14.85%) loss on the principal.

### Car payments

“Value1, value2,… must be equally spaced in time *and occur at the end of each period*.”

=*PV*(0.02,18,–15,–1000) returns the same result of $925.04. This is the most convenient way to do this problem in Excel; the tabulations are useful primarily for illustrating the effect of time on the present value of the money. Note that the present value of the bond redemption is only $700.16 rather than $764.91, the present worth if the required semiannual rate of return is 1.5%. This is the source of most of the damage ($64.75) because the biggest amount involved, $1,000, is paid farthest out, where it is divided by (1+0.02) to the 18th power.

### Other applications

This article has also covered some useful Excel functions.

1. =*PV*(*i,N,A,(F)*), where *i* is the required rate of return, *N* the number of periods, *A* the periodic payment, and *F*, an optional lump sum payment at the end of the series, can calculate the present value of a bond. The function assumes we are paying money, so we must use negative numbers for *A* and *F* if we are receiving payments.

• *PV(i,N,A)* is the function for the present worth of a uniform series.

• *PV(i,N,0,F)* is the present worth of a single future amount.

The PV function will also return the present value of a single future payment, in this case the bond’s face value; =PV(0.015,18,0,–1000) returns $764.91, noting that the last argument is the optional one for the future value.

Suppose we pay $1,000 for a 10-year Treasury note with an annual interest of 3%. The bond pays interest semiannually at 1.5%, so the coupon payment is $15. If the required rate of return on our investment is 3%, the *net present value* (NPV) of the bond is $1,000 *regardless of the remaining time to maturity*. This is because the discounted cash flows of the interest payments and the bond’s face value at redemption always equal $1,000 *as long as the available interest rate for similar bonds doesn’t change*.

“When the U.S. government guarantees a bond, it guarantees that it will make interest payments on the bond on time and that it will pay the principal in full when the bond matures. *There is a misconception that, if a bond is insured or is a U.S. government obligation, the bond will not lose value. In fact, the U.S. government does not guarantee the market price or value of the bond if you sell the bond before it matures. This is because the market price or value of the bond can change over time based on several factors, including market interest rates*.”

We can calculate this as shown in equation 1, where the required rate of return is *i*, there are *N* periods of activity, and x_{k} is the cash flow in period *k*. The reciprocal of (1+*i*) to the *k*th power is the *present worth factor* for a single payment.^{1} We can, in fact, use equation 1 in spreadsheet form to solve most if not all present-worth problems.

We can alternatively, as shown in figure 6, use Excel’s internal rate of return function to find the rate of return for the after-tax amounts just as we were able to calculate the actual interest rate on a car loan. This is more convenient than Solver if the individual annual income or payments are tabulated as shown. The IRR function operates on the highlighted cells, and an optional initial guess may be given. The result is 13.612%, which is less than the required 15%. If we use this in the formula for present worth, i.e., the after-tax amount is divided by (1+0.13612) to the *j*th power, we get $0.91, which is essentially zero, noting that we didn’t use all the significant figures in the calculated rate of return.

** **

Lean

## Introduction to Time Value of Money

### These concepts play a central role in many practical and vital applications

Suppose, for example, an investment can gain 8% per year, and we invest $1,000 a year for 30 years.

4. =*IRR(x0,x1,…,xN)* returns the internal rate of return for a series of *N*+1 payments and/or receipts; in this case, the first value (such as an initial investment) is treated as happening at time zero and isn’t discounted.

The $100,000 expenditure takes place at the beginning of the first time period. We can still make this work with =*NPV*(0.15,N8:N17)-100000, where the $18,000 payment is in cell N8, and the final transaction of $24,000 is in N17. This returns negative $5188.08.

### Internal rate of return, or return on investment

Excel has a built-in function called PMT for the capital recovery factor. The arguments are 1) the interest rate; 2) the number of periods; and 3) the present value of the principal. =*PMT*(0.005,360,340000) returns $2038.47 in red, which means it is a payment rather than income. This function can also return a monthly car loan payment, given the interest rate, number of payments, and price of the car (or price minus down payment).

### Assessment of a project or investment

Many SVB depositors didn’t invest in certificates of deposit but used their accounts instead to collect receivables and pay expenses and obligations, such as wages. When those depositors needed their money, SVB couldn’t come up with it. The bank looked to other assets, such as its 10-year Treasury notes with principal guaranteed by the federal government, only to find that this relates to the distant future rather than right now. The U.S. Securities and Exchange Commission (SEC) warns (emphasis is mine):^{2}

Now suppose the car’s sticker price is $30,000, and the dealer quotes 75 easy payments of $600. We can use Excel’s Solver function to find the internal rate of return (IRR) that returns a net present value of zero. That is,

Time value of money calculations play a central role in many practical and vital applications. These include awareness and quantification of the effects of prevailing interest rates and time to maturity of bonds, which played a central role in the failure of Silicon Valley Bank. They are useful in the assessing mortgages and car payments, including revealing the actual cost of a car loan in contrast to what the dealer might tell us. Among their key workplace applications is assessment of projects and investments to determine whether they meet the company’s required rate of return. While the subject is beyond the scope of this article, multiple projects or proposals can be compared in this manner to obtain the best possible allotment of limited resources.

3. =*NPV(i,x1,x2,…,xN)* returns the net present value of a series of *N* payments and/or receipts, given a required rate of return *i*, but assumes they take place at the end of each period. That is, the first transaction is discounted by 1/(1+i).

Time value of money calculations, including net present value analysis, is important when selecting projects and investments. The calculations are part of the body of knowledge for some of ASQ’s certification exams. They also go a long way toward explaining exactly what happened to Silicon Valley Bank (SVB) just a couple of months ago.

Thanks,

Quality Digest