The Confusion of Fully Invested Bonds
In standard fixed-income finance, calculating the value of a bond is relatively straightforward if you are looking at the yield or the discount rate. However, what if you are a retail investor staring at a massive, complex government bond contract, and you just want to know one very simple thing: “If I give the government exactly $50,000 today to buy this discounted bond, exactly how much total cash will they hand me when it expires?”
You do not care about the mathematical percentage of the yield. You do not care about the secondary market pricing. You simply want to know the absolute total cash payout (the original $50,000 principal plus the guaranteed profit) so you can plan your retirement budget.
If you try to calculate this manually by multiplying the discount rate against the principal and adjusting for the exact number of calendar days in the bond’s lifespan, you will likely make a mistake. To instantly reveal the final, total cash payout of any fully invested discounted bond, you must use the RECEIVED function in Microsoft Excel.
Understanding the Syntax
The RECEIVED function requires you to feed it five specific pieces of data from the bond’s contract. It completely bypasses yield percentages and focuses entirely on the raw cash transaction.
=RECEIVED(settlement, maturity, investment, discount, [basis])
- settlement: The exact date you hand your money to the government to buy the bond.
- maturity: The exact date the bond expires and the government hands the money back.
- investment: The total, absolute dollar amount you are physically paying for the bond today.
- discount: The official market discount rate of the bond (the interest rate).
- [basis]: (Optional) The day-count methodology. For US Treasury Bills, this is usually
2(Actual/360).
Example 1: The Retirement Payout
Assume you are planning to retire in a few years. It is currently January 15, 2024. You decide to take $100,000 out of your savings account and buy a massive block of discounted Treasury bonds.
The government contract states that the bonds will mature on November 30, 2024 (roughly ten months from now). The official discount rate offered on the contract is 4.5%.
You need to know exactly how much total cash will be deposited back into your checking account on November 30th so you can finalize your retirement budget.
Let’s map out the variables cleanly in a spreadsheet:
- Cell A1 (Settlement):
=DATE(2024, 1, 15) - Cell A2 (Maturity):
=DATE(2024, 11, 30) - Cell A3 (Investment):
100000 - Cell A4 (Discount Rate):
4.5% - Cell A5 (Basis):
2
To calculate the exact total cash payout, click on cell B1 and type:
=RECEIVED(A1, A2, A3, A4, A5)
How this works:
- Excel calculates the exact number of days between January 15 and November 30.
- It applies the 4.5% discount rate mathematically across that highly specific timeframe.
- It instantly outputs $104,166.67.
Interpreting the Output
Unlike the PRICE function (which artificially quotes everything based on a weird $100 face value), the RECEIVED function gives you the literal, real-world dollar amount.
The output of $104,166.67 means that on November 30th, the government will hand you back your original $100,000, plus an additional $4,166.67 in pure profit. You do not need to do any further conversion math.
The Error Traps
If you use the RECEIVED function and Excel instantly spits out a #NUM! error, you have likely made one of two common mistakes:
- Chronological Failure: You accidentally typed the dates backward. The Settlement date (when you buy it) must absolutely be chronologically earlier than the Maturity date (when it expires).
- Negative Money: Ensure your investment amount (the $100,000) is written as a positive number. Some financial functions require negative numbers to represent cash leaving your wallet, but the RECEIVED function demands a strict positive value.
By mastering the RECEIVED function, you cut through the confusing percentages of bond mathematics and get straight to the only number that actually matters: the final cash payout.