You didn’t expect this to be simple, did you?
Author’s note: This article ended up being “crowdsourced” through helpful (and accurate) criticism in the comments section. The Excel formulae have been edited to reflect these better techniques. Read the comments section for more great ideas.
By David Enna, Tipswatch.com
Although I have been investing in I Bonds for more than 20 years and have been writing about them for 11 years, I never paid much attention to the “exact” way interest is calculated. I figured, I’ve got a $10,000 I Bond earning 9.62%, so in six months I’d earn $481. Close enough, right?
But if you use TreasuryDirect’s Savings Bond Calculator you may notice very slight discrepancies, even after accounting for the three-month early withdrawal penalty. The Treasury’s interest calculation is ridiculously complex and possibly a relic of ancient times when $25 savings bonds were a thing.
I don’t think TreasuryDirect ever explicitly explains the complex process, but here is a good explanation of the I Bond interest calculation from the Bogleheads Wiki:
How interest is calculated
All bond values are based on the $25 bond. A $5000 bond is worth 200 times what a $25 bond is worth; a $100 bond is worth 4 times what a $25 bond is worth. If you have a $80 electronic bond at TreasuryDirect, it is worth 3.2 $25 bonds. The $25 bond value is always rounded to the nearest penny. Thus, a $5000 bond must always have a value that is a multiple of $2.00.
Interest is computed on a $25 bond using the composite rate divided by 2 for the given six month period. For individual months within the six month period, interest is computed using pseudo-monthly compounding to produce the same result after six months. For example, if the composite rate is 2.57%, the bond value after 1 month is $25 × (1 + 0.0257/2)^(1/6) = $25.05, and after 4 months is $25 × (1 + 0.0257/2)^(4/6) = $25.21, and after 6 months is $25 × (1 + 0.0257/2)^(6/6) = $25.32.
The values of a $100 bond would be $100.20, $100.84, and $101.28 after those same time periods. Note that this ignores the 3 month penalty for redemption within the first 5 years and the restriction on redemption within the first year.
You have to love the term “pseudo-monthly compounding” and you’d have to be a genius to apply these formulae to your holdings. I mean, what the heck is a ^? But the key factor is that the interest is applied to $25, rounded to the nearest penny, then scaled up to match your current I Bond holding.
If you bought $10,000 in an I Bond dated May 2022, this would be the formula you’d use in Excel to determine the value for the first month, effective on the first day of the month after your purchase: =ROUND(25*(1+0.0962/2)^(1/6),2) . The second month would be =ROUND(25*(1+0.0962/2)^(2/6),2) . The result for month one is $25.20 and multiply that by 400 to get the investment value of $10,080. For month two it is $25.39 for a value of $10,156. I worked my way through Excel to produce this for an I Bond purchased in May 2022:
Note: What is the ROUND factor? This came from feedback from readers. If you want to incorporate rounding to the penny into the “Cumulative $25 bond value” column, you need to add ROUND to the formula, as shown in the above examples. It is important to do this if you plan on incorporating that column’s calculation into an additional formula, because this is how the Treasury does its calculations.
Read the comments section for other helpful suggestions from Excel nerds.
I used TreasuryDirect’s Savings Bond Calculator to double-check these value amounts and the October amount (actually the value on November 1) did match the total of $10,236, which is the way TreasuryDirect reports values, minus the three-month interest penalty for early redemptions. Here it is, with a $1,000 investment shown because TreasuryDirect’s calculator is for “paper I Bonds only” and won’t allow an investment input of more than $5,000.
But the tougher question and still a “great unknown” is what happens after six months, when the I Bond’s balance compounds? I couldn’t find a single source that could explain the exact formula. So I devised on of my own. It works, but it could be wrong. Ponder that, math teachers.
Here is the calculation for an I Bond purchased in November 2021, earning 7.12% for six months and then 9.62% for six months.
In this calculation, I updated the baseline $25 to a value of $25.89 and the new formula for May became =ROUND(25.89*(1+0.0962/2)^(1/6),2) . The formula for June is =ROUND(25.89*(1+0.0962/2)^(2/6),2) . And so on. Using this formula, I was able to match TreasuryDirect’s last estimate of value, which is for October on my chart but actually for November 1. (I Bond interest is earned on the first day of the month for the previous month.)
As a triple-check, I confirmed my calculations for the May 2022 I Bond values and November 2021 I Bond values on the EyeBonds.info site, which is as rock solid as any information you will find. My numbers matched up with that site’s findings.
Don’t forget …
Until your I Bond investment reaches 5 years, TreasuryDirect will always show the current value minus the latest three months of interest. You have earned that interest, but TD won’t show it to you, because if you sold out today, you’d get the amount they indicate.
In the case of the November 2021 purchase, that $10,000 I Bond is actually worth $10,856, even if TD shows you $10,604.
A Tip of the Hat to Jennifer Lammer
Lammer, CEO of Diamond NestEgg, is a YouTube video star who offers some well-explained, well-documented advice on I Bonds and other investments. She created this video to explain the complexities of I Bond interest payments and the very strange $25 baseline for all investments.
Her worksheet calculations don’t match mine — we used different starting months — but we come up with similar results, which as I love to note, would drive math teachers crazy. There is a lot of good information in this video, and Lammer makes a valiant effort to make something very complex sound simple. If anyone has further advice on this I Bond calculation, send it my way in the comments section below.
* * *
Feel free to post comments or questions below. If it is your first-ever comment, it will have to wait for moderation. After that, your comments will automatically appear.
David Enna is a financial journalist, not a financial adviser. He is not selling or profiting from any investment discussed. The investments he discusses can be purchased through the Treasury or other providers without fees, commissions or carrying charges. Please do your own research before investing.