How to Calculate Note Yield in Excel or a Financial Calculator

The yield on a note at a given price is the interest rate that makes the remaining payments worth exactly that price. In Excel or Google Sheets you find it with the RATE function, multiplied by 12 for monthly payments. On a financial calculator you enter the number of payments, the payment, the price and any balloon, then compute the interest rate.

Illustrative example throughout. The figures are round numbers chosen for the arithmetic, and the yields are not a guide to what your note will draw.

A seller can use this to check what yield an offer gives the buyer. A buyer can use it to check a bid before sending it. The functions are the same in both cases, and so are the mistakes that throw them off.

The inputs and the sign rule

1. Payments left. Count them from the amortization schedule, the table that splits each payment between interest and principal.
2. Payment. Principal and interest only. Leave out anything collected for property taxes or insurance.
3. Price. The amount paid for the note today, not the balance owed.
4. Balloon. Any lump sum due with the last payment, or zero if there is none.

Spreadsheets and calculators treat money going out and money coming in with opposite signs. From the buyer’s side, the price is paid out and goes in as a negative number, while the payments and the balloon come in and stay positive. Give them all the same sign and RATE returns an error value in place of a rate.

The example note

RATE: the yield from a price

The syntax is =RATE(payments left, payment, price, balloon). The answer is a monthly rate, so multiply by 12 for the annual figure:

=RATE(120, 950, -72000)*12

That returns about 9.96%, or 10% in round terms. The same formula on other prices shows how quickly yield moves:

The first row is your check. Priced at the balance, a note yields its own interest rate. If your version of that row does not come back at the note rate, the payment or the payment count is wrong, and nothing else in the sheet can be trusted until it does.

The gap between rows is the point for a seller. A $2,000 difference between two offers on this note is a difference of more than half a point in what the buyers want to earn, which is the comparison comparing offers on your note is built on. If you want offers to run the formula on, you can send your note for pricing; each offer comes back with the balance, the buyer’s yield and the discount.

PV: the price from a yield

PV runs the sum the other way. Give it the monthly yield, the payments left and the payment, and it returns the price:

=PV(0.10/12, 120, -950)

That gives about $71,890, which is the $72,000 offer in reverse. At 9% the same formula gives about $74,990, at 11% about $68,970 and at 12% about $66,220. The full treatment of discount rates and present value is in how to calculate the present value of a note.

PMT: checking the payment

PMT confirms that the payment you entered matches the note’s original terms. Suppose the note started at $105,700, at 7%, over 15 years:

=PMT(0.07/12, 180, -105700)

That returns about $950. Sixty payments have been made, which leaves the 120 used above. If PMT gives a different figure from the one your payer sends, find out why before you calculate anything else. The usual reasons are an escrow amount added to the payment or a term that was changed along the way.

Notes with a balloon

Add the balloon as the fourth entry, positive because the buyer receives it. Take a note paying $600 a month for 60 more months with a $45,000 balloon at the end, offered at $50,000:

=RATE(60, 600, -50000, 45000)*12

That returns about 12.97%. Leave the balloon out and RATE answers a different question about a note that does not exist.

Annual and effective yield

RATE times 12 is a nominal annual yield, the convention most note offers use. Because payments arrive monthly and can be reinvested, the effective annual yield is slightly higher. Excel’s EFFECT function converts one to the other: =EFFECT(9.96%, 12) gives about 10.43%. Neither figure is wrong, but comparing one buyer’s nominal yield with another’s effective yield makes the first look cheaper than it is. Ask which one an offer states. What yield on a note means sets out the difference between yield and the note’s interest rate.

On a financial calculator

Clear the time-value registers first, because a figure left over from the last problem is the most common calculator error. Then enter:

• N = 120
• PMT = 950
• PV = -72,000
• FV = 0, or the balloon if there is one

Compute I/Y. Many calculators come set to 12 payments per year, in which case the answer shows as the annual figure, 9.96. If yours is set to one payment per year, or works in periods as the HP 12C does with its i key, the answer is the monthly rate, about 0.83, and you multiply by 12. Price the note at its own balance as a check, exactly as in the spreadsheet.

Irregular payments and common mistakes

RATE assumes equal payments at equal intervals. When the payments step up, skip months or arrive on odd dates, list each amount beside its date with the price as a negative on the closing date, and use XIRR. XIRR returns an effective annual yield, so compare it with an EFFECT result and not with RATE times 12.

The mistakes that come up most are simple. An annual rate gets entered where a monthly one belongs, escrow gets counted in the payment, the balance goes in where the price should be, or the count of payments left is off by a few. Each one moves the answer far enough to change which offer looks best, so run the check row every time and keep the note value guide open for the terms behind each input.

Related questions