**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.

• **Confused by I Bonds? Read my Q&A on I Bonds**

• **Inflation and I Bonds: Track the variable rate changes**

• **I Bond Manifesto: How this investment can work as an emergency fund**

* * *

*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.*

Pingback: December newsletter - Glassner Carlton Financial

Pingback: I Bonds vs. TIPS: Right now, it’s clearly ‘advantage TIPS’ | Treasury Inflation-Protected Securities

Pingback: Short-term I Bond investors: Be patient with your exit strategy | Treasury Inflation-Protected Securities

I’m a little confused on how the new rate affects my I bonds purchased in April 2021. Specifically, the .40 base? Somewhere I got the impression that eith the reset I will just receive the 6.48%. Is that true? Or did I misunderstand something?

The new fixed rate does not apply to purchases in April 2021. That fixed rate of 0.0% is permanent and your new composite rate will be 6.48%, beginning in April 2023.

Thank you for clarifying for me.

Very helpful. Thanks. But when calculating for the new rate (6.48% using $27.14 from month 12 for months 13 thru 18) the values do not match the eyebonds.info site for months 2 thru 6 of the 6 month period. I may have an error in my spreadsheet, but if so I can’t find it. The dollar amounts are off by $1 to $2 each month.

I did find a mistake in my spreadsheet. Your approach works.

I do see the eyebonds.info is listing the new composite rate at 6.48%. Is that right?

Thanks again.

I’m seeing 6.89% on that site for the composite rate, which is correct.

Thanks. I was referring to the composite rate for the May 2022 bond. The new .4% fixed rate (and 6.89% composite rate) for the November 2022 bond threw me.

6.48% is correct for the May 2022 bond.

Beautiful. I was just looking at this today because of the 6 month aniv. and changeover and got totally confused by why the value was understated. Of course I googled and chance would have it that I got THE PERFECT ANSWER and explanation right up top. Kudos on this work.

Is there a spreadsheet available that would calculate the value of an IBond portfolio after inputting the purchase dates and bond values? I realize the user would have to plug in the interest rates for each May & November going forward.

Thank you for your articles on this topic.

I am sure some of my financial genius readers have built a spreadsheet like that. However, a good option for everyone else is to use TreasuryDirect’s Savings Bond Calculator: https://www.treasurydirect.gov/savings-bonds/savings-bond-calculator/ . TD says this is for “paper I Bonds only” but it works fine for all I Bonds. But you have to enter a $10,000 purchase in as two $5,000 purchases. I wrote a user’s guide back in 2018: https://seekingalpha.com/article/4179171-step-step-guide-to-using-treasurys-new-savings-bond-calculator

Thank you so much! Too. Infusing to process but you assured me to trust the process! And then showing the balance within the 3 year period makes sense too. Much appreciated!

Nice discussion of the topic. Thanks.

The Treasury Direct FAQ and your article say that the early redemption penalty is the latest 3 months interest. It looks like in practice it is actually the first three months. This is a very big deal given the very high rates at issuance. If rates revert to the mean and I redeem, I would definitely prefer to lose .5% over 2.4%.

It is the last 3 months. Without question.

On what basis do you make the claim that “in practice it is actually the first three months?”

Pingback: Ignore the fixed-rate drama; buy I Bonds in October | Treasury Inflation-Protected Securities

QUESTION

Do the number of days in the month make any difference?

I Bond interest is based on months, not days.

I’m 99% sure that your guess as to what happens when the interest rate changes is incorrect, although it made no difference in your example. Instead of using the rounded value of the $25 I bond at the end of month 6 as the updated baseline for months 7-12, I think you should use the unrounded value. It just so happened that for 7.12%, the rounded value is exactly equal to the unrounded value of $25.89. Not so with 7.13%

Have you seen documentation for this? I’d love to see the exact formula for the 6-month compounding.

No documentation is why I’m 99% sure, not 100% We’ll just have to confirm that it works over a longer time period, but I think this formula works for EVERY month, not just the months when the interest rate changes: Next month’s value=(face amount/25)x(this month’s unrounded value of a $25 bond x (1+next month’s pseudo rate), rounded). Then next month’s interest = next month’s value minus this month’s value. I can send a spreadsheet in reply if you email me.

I have checked my formula against TreasuryDirects’s Growth Calculator, for a $25 bond over 30 years, and interest rates of 2%, 4%, and 6%, and my results agree with their results to the penny. So now I’m 99.99% sure of my formula. The formula you used, using the rounded values of the $25 bond for the 6 month rate updates, was off by pennies in each case. This would mean that despite TreasuryDirect’s assertions, interest is really compounded monthly (because of the pseudo rates) NOT every six months.

Oops. I was 99.9% sure I had it, but I was 100% wrong. Your guess was absolutely right. When a new interest rate takes effect for 6 months, use the rounded value of the $25 I bond at the end of the previous 6 months for the calculations. (I had said to use the unrounded value.) The documentation for this is actually found in the 31 CFR 359.39, where it provides the formula

FV = PV × {[1 + (CR ÷ 2)] ^ (m ÷ 6)},

which is rounded to the nearest cent. The rounded FV for m=6 becomes the PV for the next 6 months, so PV is a rounded number.

Redemption values for I bonds are published online at https://fiscaldata.treasury.gov/datasets/. I corrected my spreadsheet of values for the $25 I bond and was able to verify that my resulting October 2022 redemption values for every I bond series ever issued exactly matched the published redemption values.

Thanks for this update, Tom. It seemed logical that the calculation would carry-forward the rounded amount to create the new baseline. I’m willing to just trust the Savings Bond Calculator to get it right, but it is good to know the formula behind it all.

Tipswatch, thanks for your amazing article. I started making my way through it and don’t understand why in every month for the first 6 months the value increased by $60 except for December. I haven’t made my way through all of the other comments, but maybe someone else noticed it as well, or maybe I just don’t understand it yet. Still, you made something I thought was simple into something less complex, and I thank you.

I think this comes from the effect of the “pseudo-compounding” formula combine with rounding the baseline number. In the first six months, December gets $56 while the other months get $60, and in the second six months May and August get $80, October gets $88 and the other months get $84. In Jennifer Lammer’s YouTube video she showed the monthly increases for a different scenario, and they also varied by a few dollars at times, because of the formula.

For complete generality, formulas need to further round off the interest calculation after multiplying by the face value divided by 25. You can buy I Bonds online in any denomination of at least $25, such as $2,561.35, which is equivalent to 102.454 of the $25 I bonds. If a $25 I bond earns 21 cents in some month, 102.454 times 21 cents is $21.51534, which Treasury Direct would further round to $21.52

Seems like the Savings Bond Calculator (SBC) could be a reasonable alternative for some people in some cases. Possible downsides, depending on one’s situation and preferences are: 1) requires access to the internet; 2) need to go to their website; 3) need to do a bit of data entry on the website and then also data transfer to your own records; and 4) the calculator is limited to using only the denominations that are available when buying paper bonds, so if you have a different denomination you’ll have to do some extra calculations to make it useful. For electronic iBonds at least, for someone who is only interested in the current redemption value (from which they can also easily calculate the current total interest amount), it seems like it would be just as easy or easier to just login to their account and look at the list there. For myself, the one feature of the SBC that sounds like it might potentially be helpful in the future is to create an inventory list of my holdings, but at the present I don’t really have a high enough number of different holdings for that to make sense. Thanks for mentioning this though, Patrick, as I know others have already and will find it to be a helpful tool.

Calculating the value of an I-Bond is so much simpler using a spreadsheet. The magic formula provided by Jennifer and David — with the six-month pseudo-compounding based on the fractional exponent — is correct and produces results that are identical to TreasuryDirect’s calculator, right down to the penny every month. Why do it many times when you need only do it once?

I agree, hoyawildcat, but I’m guessing the initial setup (and any related troubleshooting) can look a bit daunting to anyone who has only a limited knowledge of spreadsheet programs and their formulas. Having said that, I think what you’ve provided here provides a huge ‘shortcut’ for someone like this, giving them a tool / resource they can use to expand their spreadsheet knowledge / skills while also tweaking it into a specific solution for their own needs. Depending on their starting point, they’ll still need to put some time into it, but it will be a lot less time than if they were just starting by staring at a ‘formidable’, ‘mystifying’, and very blank spreadsheet. 🙂

Dustin, I use the Savings Bond Calculator (SBC) because I like to know my monthly interest for each month for the next six months. One need use it only twice a year, when the rates reset. You only have to input the purchase price of the bond and the month and year of the purchase. You can combine bonds purchased in the same month and year. Once you get it set up, it is very easy to get the amount of interest for the next months. I am only interested in the total interest for all my bond holdings. I don’t care too much about interest for an individual bond, although you can see it if you want.

BTW, I checked the SBC using an HP 15C calculator many years ago and it was correct. The SBC is of course a lot easier to use (though somewhat clunky, but hey, it is a government program). I admire your efforts and stamina.

Patrick, that sounds like an approach that definitely works for what you are wanting to accomplish. (I have no doubt the SBC is accurate.) Am I correct in assuming that the denominations of bonds (or combined totals of bonds) that you need to enter always matches an amount that’s available in the SBC’s drop-down menu (i.e. the amounts available for paper bonds)? If so, that would definitely simplify the process. And when you say “once you get it set up”, are you talking about just entering the data in the SBC, or also about some sort of file / spreadsheet you maintain on your own? Thanks for providing additional color in regards to your approach to this.

Dustin, I think sometimes I have to enter a $10,000 bond as two $5,000 bonds, but I don’t think much about it, I just do it. I just make sure the amounts of the bonds all add up to what they should be when I am finished inputting.

“Once I get it set up” is a little unclear. I should have said “once I input my data into SBC”, meaning the bond purchase price and the date of purchase. Then I just change the month and year in the SBC to which month and year I want the interest. SBC gives me the total (principal plus interest), but I simply subtract the previous month from that to get the monthly interest. If you change the month and year in the SBC too far in the future (like 7 months) it conveniently and logically gives an error message. I type in the total interest I get from SBC for each month into Quicken, which keeps track of everything (bank accts, I-bonds, etc.). I have a special row in Quicken just for I-bond interest.

I have maybe 30 or 40 I-bonds, but I combine the $1000 ones that had the same purchase date. Remember I only care about total interest for all my holdings and don’t care to see the interest for each bond, although that is possible. I would say it takes about 15 minutes for the whole process at each rate reset to get interest by month for each of the next six months. In other words, it takes about a half an hour each year. Most of that time is spent inputting the bond info.

I also like to do this to make sure nobody has been messing with my Treasury Direct account (I should probably look more often). There is a part of the web site which gives total principal plus interest for the current date, which I look at quickly to make sure the total matches (I suppose you could look at that once a month to see your interest).

To be clear, I do not use a spreadsheet. I use SBC to generate the interest, and Quicken to record the interest. I have a record of monthly interest back to 2001.

Thanks, Patrick, for taking the time to share how you manage your iBonds. It’s interesting to me to see the different approaches that people take to this. Now all you have to do is convince Treasury Direct to offer a complimentary service that automatically downloads the info you want into your Quicken program each time there’s a rate reset. 😉

Dustin, I wouldn’t hold my breath on that download to Quicken. But just as well. I really only have to type six numbers into Quicken (one for each month) and I only have to do that twice a year. I like the way my approach gives me my future earnings up to six months out, I only have to do it twice a year, I don’t have to mess with a spreadsheet, and it’s easy (easy for me anyway).

As I have noted, I do use the Savings Bond Calculator to track the value of my I Bonds and I only perform updates about once a quarter when I am considering re-balancing my portfolio. It’s a fine tool, and I wrote a guide to getting started for SeekingAlpha back in 2018: https://seekingalpha.com/article/4179171-step-step-guide-to-using-treasurys-new-savings-bond-calculator

This falls under the category of “everyone, including the government, making things harder than they had to be”, and I say this as someone who actually IS a math teacher!

The biggest confusion above seemed to be how to handle the compounding for I-bonds held more than 6 months. And based on some playing around in Excel and comparing it to a thankfully-previously-downloaded I-bond value chart, it seems like you came up with the right idea. The most direct way to do the calculations, for every half-year interval (except the last if it’s incomplete), is to ignore the “pseudo-monthly compounding”, and just do a two-step process.

1. Take the incoming $25 bond value, and multiply it by 1 + the semi-annual growth rate.

2. Round to the nearest cent.

If the final interval isn’t complete, then you need (1+SAGR)^(M/6) as the factor in step 1. If you haven’t held it for 5 years, then just pretend it’s three months ago.

Given that the compounding really only happens every six months, it would make a lot more sense if they treated the interest as simple throughout that interval. That is, if a bond is to accumulate $2.16 of interest in a six-month interval, it gains 36 cents each month. Oh well.

And at the end of the day, what is the end result of this needless complexity? I investigated the value of two $1000 I-bonds, purchased in July 2015 and July 2018, as of July 2022. The true values, which matches my spreadsheet calculations, are $1162.00 and $1104.80. If there was no intermediate rounding, and we just did $1000 * (1+rate1) * (1+rate2) * … etc., the values would be $1161.86 and $1104.65. A difference of literally pennies (and it’s actually a difference in our favor!)

If anyone is interested, I can provide step-by-step instructions for a simple Excel spreadsheet that will show the monthly value of an I-Bond over its 30-year term and also show the redemption value for any month (including the three-month penalty during the first five years).

I would be interested in seeing those, hoyawildcat.

Sorry, can’t do it because my posts are not showing up.

No problem; thanks for trying.

I am posting this for hoyawildcat, since it might be too long for WordPress to handle. I have not tested this at all, but here it is:

Here’s a way to create a simple Excel spreadsheet that will calculate the value of an I-Bond over its 30-year term and show you the redemption value in any month (including the 3-month penalty).

1. We will use columns A through L. In most cases you will only need to enter data or formulas in rows 1 and 2 and then copy-down the columns in row 2. Format column A as a date showing the month and year (i.e. MMM-YY). Format columns B as a percentage with two decimal places. Format columns D through L as currency with two decimal places.

2. In cell A1, enter the first month when the new I-Bond interest rate was set, e.g. May-22 in this example. In cell A2, enter “=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))”. Copy-down cell A2 through cell A361, which will give you 30 years plus one month of monthly dates.

3. In cell B1, enter the interest rate as of the date in cell A1, e.g. “3.54%” for May-22. In cell B2, enter “=B1”. Copy-down cell B2 through cell B360. In cell B7, enter “7.12%”, i.e. the interest set in Nov-22. In cell B13, enter “9.62%”, the interest rate set in May-23. (You can also enter your guesstimates for future interest rates in cells B19, B25, etc.)

4. Select the month you purchased the I-Bond, e.g. Oct-22 (row 6) in this example. In column C6, enter “1”. In cell C7 enter “=IF(C6=6,1,C6+1)” Copy-down C7 through C360. Note that the numbers range from 1 through 6 and then reset. We will use these values in the magic formula for the numerator of the fractional exponent, e.g. 1/6, 2/6, and also for the semi-annual compounding.

5. In cell D6, enter “$25.00”. In cell D7, enter “=IF(C7=1,E6,D6)”. Copy-down C7 through C360. Don’t worry about the calculated values for the moment.

6. In cell E6, enter the magic formula: “=ROUND(D6*(1+$B1/2)^(C6/6),2)” Copy-down E6 through E360. (Note that this will reset the values in column D and also show the semi-annual compounding.)

7. In cell F6, enter “=E6-D6”. Copy-down F6 through F360. (This is the cumulative interest during each six-month period.)

8. In cell G6, enter “=IF(C6=1,F6,F6-F5)”. Copy-down G6 through G360. (This is the interest earned each month.)

You now have all the info you need to calculate the monthly value of your I-Bond. For a $10K bond, you simply need to multiply the values in rows D through G by 400 (i.e. 10000/25). We’ll do that now.

9. In cell H6, enter “=D6*400”. (You can use a constant for the multiplier if you want to play with the bond value. For example, enter the bond value (10000) in cell N2. In cell N3, enter “=N2/25”. Cell N3 will contain the multiplier. You can use that by entering “=D6*$N$3” in cell H6. Be sure to enter an absolute cell address — $N$3 — so that when you copy-right and copy-down the cell address of the multiplier will remain constant.)

10. Copy-right H6 through K6. Copy-down cells H6 through K6 to H360 through K360. You now have the bond value and interest earned for each month of its 30-year term.

11. We will now calculate the actual redemption value in each month including the 3-month penalty during year the first five years. In this example, the I-Bond was purchased in October 2022. Therefore, the first month you can redeem it is October 2023. In cell L18, enter “=I15”. This was the bond value as of July 2023. Copy-down L18 through L66, which corresponds to October 2027.

12. In cell L66, enter “=I65”. Copy-down L67 through L361. Column L now contains the redemption value of the I-Bond by month.

13. For better readability, insert a new row above row 1 and enter column headers in the newly created row.

Thanks.

Ok, I was game. I followed your instructions. It’s an elegant way of doing things. Just a few things I noticed:

1. Steps 3 & 4: The interest rates are off when the example investment begins in Oct-22. There’s one month at 3.54%, then it switches to 7.12%.

2. Step 4: In cell C6 (not column).

3. Step 6: The magic formula is incorrect. It should be (C6/2), not (C7/2).

4. Steps 11 & 12: The L66 and L67 formulas seem off.

🙂

1. Steps 3 & 4: I don’t see that. Cells B1 through B6 should be 3.54% and cells B7 through B12 should be 7.12%. If they are then the interest is calculated correctly (after you fix the magic formula as described below).

2. Step 4: Yes, it should be “cell C6.”

3. Step 6: Sorry, that was a typo. The magic formula in cell E6 should be “=ROUND(D6*(1+$B1/2)^(C6/6),2)”. Enter that in E6 and then copy-down E6 to E360.

4. Steps 11 & 12 are correct. You don’t earn any interest in the redemption month.

Why do you guys keep going over this for less than a price of a hamburger over 6 months. Its actually down to pennys. So is it safe to say that if I have a 10k I bond and it pays 9.62 for 12 months that at the end of the term I should have in my account 10,962 minus about 240 for the prior three months for a total of 10,722 give or take a few dollars. I mean you could figure monthly without all this back and forth.

Mike, I’m a retired software engineer. Old habits die hard.

Ok, I have the interest rates set as you instructed. But, in Step 4, you use Oct-22 as the purchase month. So all the formulas are set at line 6, which is also the last 3.54% line. If I leave the interest rates as is, things are off. I would need to adjust the rates to the purchase month?

In Step 11, you say to copy something down through L66, but in step 12, you changed the formula for L66. Is that correct?

Jack, did you enter the new interest rates for Nov-22 and May-23 as described in step 3 ?

Step 11 provides the redemption value for years two through five with the three-month interest penalty. Step 12 provides the redemption value after five years with no interest penalty. Note that you will not earn any interest in the month that you redeem the bond. Jennifer’s video discusses this in some detail.

Hi. Can you clarify in Step 3 why you say the interest rate as of May 2022 is 3.54% , the interest rate set in Nov 2022 is 7.12%, and May 2023 is 9.62% ?

When I look at a table of previous and current rates, they show as Nov 2021 – Apr 2022 7.12%, May – Oct 2022 9.62%, Nov 2022 – Apr 2023 as 6.89%.

I’m trying to understand why in my interest rate column I wouldn’t use 9.62% for May 2022 and why I should use 3.54% as your example.

I bought my I-BOND in Mar 2022. Wouldn’t my excel sheet therefore begin Mar Apr 2022 @ 7.12%, then May thru Oct 2022 @ 9.62% and finally Nov 2022 – Apr 2023 as 6.89%?

I’m totally lost why (I’m sure) I’m not looking at this correctly. Help pls!

Thanks

Not sure exactly what you are asking, but if you bought in March 2022, you got a full six months of 7.12% and then in September 2022 you began earning six months of 9.62%. The new variable rates begin on a six-month schedule that starts in your month of purchase. … And you will never earn the 6.89%, because that includes the 0.40% fixed rate for I Bonds bought from November 2022 to April 2023. Instead, you will earn 6.48% for six months.

Thanks for the reply. What I was asking was in reference to what hoyawildcat said (but posted by you on their behalf) in their example of how to set up the Excel sheet with their method.

Step 3 says:

“In cell B1, enter the interest rate as of the date in cell A1, e.g. “3.54%” for May-22.”

But the rate for May-22 was 9.62%

Then again, the example given for v Cell B7 is:

“In cell B7, enter “7.12%”, i.e. the interest set in Nov-22. ”

But the rate in Nov-22 was set at 6.83%

Clearly I’m missing something fundamental here. But I can’t see what that is.

Is it not true that the rate from May -22 to Oct 22 was 9.62%? (According to a historical table I’m looking at). I’m having trouble understanding why hoyawildcat says to put 3.54% for May 22 and copy that down to Cell B7 in their example.

Hopefully my question is clearer? Thanks!

Sorry, that was a typo. It should have read ‘In cell B1, enter the interest rate as of the date in cell A1, e.g. “3.54%” for May-21’ and ‘In cell B7, enter “7.12%”, i.e. the interest set in Nov-21.’

Thanks for pointing it out.

Ah thank you so much for the reply! I was all coffee’d up ready to tackle your awesome detailed guide for a comprehensive Excel sheet and I got stuck on the interest rate/date example. Feel much better that it was just a typo! 🙂

One other question if possible:

If I purchased I-Bond in March 2022, that means I get the 7.12% rate. But is that only lasting for Mar & April 22 and then switches to the new rate set in May 22 @ 9.62%? Or should I be calculating 6 months at 7.12% (Mar 22 – Aug 22) and THEN start using the new rate in Sept 22, for the next 6 months, even though it was set in May?

Apologies for the dumb question! Thanks

Each interest rate lasts for six months, regardless of when you purchased the I Bonds during that six-month interest rate timeframe. So, if you purchased in March 2022, then you got the 7.12% (annualized) rate for six months, through August 2022, and in September 2022 it was raised to 9.62% for the next six months, etc.

Thanks for helping to get this posted, David. And thanks, hoyawildcat, for investing your time in sharing your Excel skills with us in this way. I am definitely going to be saving these instructions in case I ever decide to use this type of setup, and also as a practical tool / project for increasing my own ability in using spreadsheet software. As a sidenote, it’s interesting that working with spreadsheets is not all about knowing the functions and formulas — there’s a fair amount of design work involved, and it’s usually worth putting time and thought into coming up with a good design for whatever solution is needed. I guess in that sense it’s a bit like software engineering.

PLEASE NOTE: There’s typo in the magic formula in step 6. “(C7/6)” should be “(C6/6)”.

Here’s the correct instructions:

6. In cell E6, enter the magic formula: “=ROUND(D6*(1+$B6/2)^(C6/6),2)” Copy-down E6 through E360. (Note that this will reset the values in column D and also show the semi-annual compounding.)

Sorry for the mistake.

David fixed the error in step 6 of the original instructions.

No problem, hoyawildcat. I’ve been saving the notification emails of the follow-up comments here. For some reason, I don’t get notifications on all of them, but I can always come here again to see if I’ve missed anything. Thanks to Jack for his testing / reporting, and to you and David for your prompt corrections.

For my setup, I also found it helpful to calculate the penalty amount, which could then be used to adjust the current value for bonds held less than five years. The mental approach I used for this was to calculate the total interest accrued for at least the previous three months and then subtract any amounts from months prior to that whose accrued interest would no longer be included in the penalty. This is a bit of a different approach than this article uses, which is to add the accrued interest for a month in whichever month it is shown by Treasury Direct (TD), but they both give the same result. I’m obviously biased ( 🙂 ), but I like my approach a bit better because it ‘feels’ like it more clearly represents what TD actually does when it applies the penalty. However, I do recognize that the associated calculation(s) / formula(s) are a bit more complex. There may be more elegant ways to do it, but following are some example formulas for calculating the penalty amount.

Example #1:

* Bond Details: $1500 principal, purchased in March 2022, 7.12% initial interest rate, results will show penalty applicable in September.

* Prerequisite: Use the formula =ROUND(25*(1+0.0712/2)^(6/6)-25,2)*1500/25 in the cell of your choice (I’ll use F8) to calculate the total interest accrued, as of September. (This simplifies the process because now I can use ‘F8’ in the penalty-related formula to include this formula, including any future changes to it.)

* Formula for penalty amount: =F8-(ROUND(25*(1+0.0712/2)^(3/6)-25,2)*1500/25) (English translation: 1) I’ve determined that I want to exclude the first three months of interest in this six month period, so I use the second portion of the formula [with the ‘^(3/6)’] to calculate what that amount would be. 2) Then, I subtract this amount from the result of the formula in F8, which uses the ‘^(6/6)’, which then gives me the amount of interest accrued in the last three months of the period, which is the penalty amount.) (Note that this specific formula is one that can be used to test whether your ‘ROUND’ function is working as expected. If it’s working, the result is 27.00; if not, it is 26.93.)

Example #2:

* Bond Details: $1500 principal, purchased in June 2022, 9.62% initial interest rate, results will show penalty applicable in September.

* Prerequisite: Same as above, except use the formula =ROUND(25*(1+0.0962/2)^(3/6)-25,2)*1500/25 .

* Formula for penalty amount: =F8-(ROUND(25*(1+0.0962/2)^(0/6)-25,2)*1500/25) (English translation: 1) I’ve determined that I don’t need to exclude any months of interest in this six month period, so I use the second portion of the formula [with the ‘^(0/6)’] to reflect that. [This may seem unnecessary because it’s the same effect as subtracting ‘0’, but I want to preserve the structure of the formula for future updates to it.] 2) Then, I subtract this amount from the result of the formula in F8, which uses the ‘^(3/6)’, which then gives me the amount of interest accrued in the first three months of the period [i.e. the same amount as what’s in ‘F8’], which is the penalty amount in this case.)

* The main value of this example is to show that ‘^(0/6)’ can be used when you don’t need to subtract any accrued interest (i.e. when you’ve only accrued interest for three months or less in a particular period).

Example #3:

* Note that I won’t be able to check / test this formula until October, but I think it ‘should’ work. 🙂

* Bond Details: $1500 principal, purchased in March 2022, 7.12% initial interest rate, 9.62% subsequent rate, results will show penalty applicable in October.

* Prerequisite 1: Use the formula =ROUND(25*(1+0.0712/2)^(6/6)-25,2)*1500/25 in the cell of your choice (I’ll use F8) to calculate the total interest accrued at the 7.12% rate.

* Prerequisite 2: Use the formula =ROUND(25.89*(1+0.0962/2)^(1/6)-25.89,2)*1500/25 in the cell of your choice (I’ll use F9) to calculate the total interest accrued at the 9.62% rate.

* Formula for penalty amount: =F9+F8-(ROUND(25*(1+0.0712/2)^(4/6)-25,2)*1500/25) (English translation: 1) Because the formula structure is preserved in the section using the 7.12% rate, I’ve simply added ‘F9’, which represents the one month of interest accrued at the 9.62% rate. 2) I’ve determined that I want to exclude the first four months of interest in this six month period, so I use the second portion of the formula [with the ‘^(4/6)’] to calculate what that amount would be. 3) Then, I subtract this amount from the result of the formula in F8, which uses the ‘^(6/6)’, which then gives me the amount of interest accrued in the last two months of that period, which is the part of the penalty amount at the 7.12% rate.) 4) Once this amount gets added to the ‘F9’ amount, this results in the total penalty amount.

* The main value of this example is to show how the penalty amount can be calculated when it involves two different six-month holding periods.

Additional notes:

* Even though you need to adjust the baseline amount in the formulas when entering a new six-month period (eg. 25.00 to 25.89), there is no need to change the ‘scale-up’ portion of the formula (eg. ‘*1500/25’) because that ratio will stay the same and give the same result whether you adjust it or not.

* If you are wanting the formula to give a result that includes the principal amount, you can just delete the part of the formula that subtracts the baseline amount (eg. ‘-25’ or ‘-25.89’). Alternatively, if using the formulas mentioned above in Example #1, and assuming that the principal amount is in cell F7 and the penalty amount is in E8, then you can use the simple formula =F7+F8-E8 to get the total current value for a bond. (If using this simpler formula, you just need to remember that the principal amount [represented here by ‘F7’] will be reset at the beginning of each new six-month period.)

* Obviously these formulas will become ‘obsolete’ at the beginning of each month, but once you have them in place, it shouldn’t take too much time to tweak them whenever you want to update / check current values.

* Because I am mainly interested in current values, I just use one row in my spreadsheet for each six-month period of accrued interest, tweaking the formula as necessary within those six months whenever I want to update the current value.

Some may find this to be way too much detail and too complicated; others may be thinking “There’s a lot simpler way to do this; just use this function / formula…”, etc. But here it is, in case others might find it helpful. 🙂

Dustin, I love your passion and thank you for your help. For most people, I think, just understanding how the Treasury does its calculations should be enough, and that the calculations are accurate. It can be annoying to see amounts varying by a few dollars from what you think is right. I use the Savings Bond Calculator a few times a year to update values, without worrying if the information is accurate.

You’re welcome. Yes, that makes sense; I noticed that from some of the other comments as well. 🙂 All the best.

As I mentioned before, just use the Savings Bond Calculator. I use it just twice a year, when the rates are reset. This will calculate your interest for each month out to six months. It will tell you when you can’t go any further. It is accurate and easy to use.

I tried out your Example #1. You put a lot of work into it and I was game to give it a go.

Anyway, yes, it works the way you did it and probably for the way you built your table. And like Jennifer says, joy! However, if a person sets up their table the same way David did, which I did, then a person could just subtract what “TD shows” from the “Value of investment.”

For instance, looking at David’s “$10,000 I-Bond purchased Nov 2021” table above. If a bond was cashed in on May 4, 2022, the amount received would have been $10,176. Just subtract that from the “Value of investment” of $10,356 to arrive at forfeited interest of $180.

David’s months are set up as 1 day after month’s end, so actually the 1st of the following month. If the bond was cash in on May 4, the figures for April would have been used (in his table).

If I person didn’t want to set up a table, your formulas work well on their own (as long as one remembers to change certain values within the formula) for finding the amount of the interest penalty.

Thanks for sharing your experience with it, Jack, and I agree with what you said. I already had my spreadsheet set up before I came across this article, so that’s what influenced me in this direction. And thus, the reason I wanted to calculate the penalty was to use that amount in a formula to calculate the value Treasury Direct would show, so a bit of a chicken-and-egg scenario when comparing it with the tables here. 🙂 Also, the tables that David and Jennifer use in their presentations are great for their purpose of illustrating how the calculation is done as you move through time. However, for record-keeping purposes, the approach is a bit cumbersome, since you would need to have six rows for each six month period rather than just one. (Although if someone wanted to do all the data entry for each month, they could then just hide the first five rows at the end of the six-month period to lessen the amount of scrolling needed.) But I guess it all depends on what someone’s goal is, and what feels most intuitive to them for their situation, etc. How do they say, “There’s more than one way to skin a cat.” 🙂

OK just a nit: your statement: “In the case of the November 2021 purchase, that $10,000 I Bond has actually earned $10,856” It didn’t EARN $10,856, actually it EARNED $856. It is now WORTH $10,856. Sorry, as an ex-engineer, I just can’t help myself :}

Excellent point. Fixed.

FYI, readers: Thanks to your great feedback in these comments and emails, I have updated this article to include the formula used to round the “Cumulative $25 bond value” column. If you see any problems with that, let me know!

Not sure the new formula works. I substituted it for what I have, and I’m back to being off $-1.39. Is it possible the ROUND function has to be at the beginning of the formula, because that’s what works for me. Like this: =ROUND(25*(1+0.0962/2)^(1/6),2).

I’m at best a dabbler in Excel and have little idea of what I’m doing. But I managed to get my numbers to exactly match yours. As Jennifer might say, that gives me joy.

Yes, David, I think Jack’s right. I’m pretty sure the effect of ‘+ROUND(0,2)’ in your formula is to just add ‘0’ (i.e. ‘plus the number ‘0’ rounded to two decimal places’). Perhaps it ‘worked’ again for you because your sample calculations don’t need the rounding to be done, not sure. Another tidbit from Jennifer’s video that was quite helpful for my setup was how to figure just the interest portion, which you can do by adding ‘-25’ at the end of the basic formula (to back out the principal amount), like this: =ROUND(25*(1+0.0962/2)^(1/6)-25,2) . And then if you want to put everything in one formula to figure the total scaled-up interest amount, you could use this (for a $1,500 bond): =ROUND(25*(1+0.0962/2)^(1/6)-25,2)*1500/25 . And if you wanted it to include the principal amount as well, just delete the ‘-25’ that was added, etc. Hope that helps a bit. 🙂

This is great, and thank you all. When I can get back to my computer I will make this fix. The reason my 400x calculations worked was because I just re-entered the dollars/cents number from the $25 column into the next equation x 400, so I was locking in the rounding by doing that. My wife (a former CPA) is way, way better at Excel than I am.

Hey, thanks for explaining that. I did wonder how you got the correct answer without seeming to use the ROUND function.

Looking forward to your post about the upcoming 5yr TIPS.

That makes sense. I had considered that as one possible solution to locking in the rounding, but decided I wanted something more automated to save time in data entry, since there’s a chance I’ll be wanting to use this spreadsheet setup for the long-term. In regards to spreadsheet programs like Excel, all I have to do is look at a list of all the functions that are available, and then I realize how much I don’t know about the various things that the program can do. It’s nice that these programs come with ‘Help’ info and you can usually find help via an online search as well, although it can take some time (at least for me) to wrap your head around what a particular function does and how it’s meant to be used. For me, when I’m trying to figure out something new, the biggest step is trying to determine whether the program can actually do what I want it to do, and then assuming it can, trying to figure out which function(s) will get me the result I want.

Excel has a very rich set of functions that can do practically anything you want. (In my opinion Excel is Microsoft’s best product.) The only time I ever had to write my own Excel VBA functions was to calculate MAX and MIN using absolute values, i.e. when both positive and negative numbers were involved. (I wanted to calculate the MAX and MIN variance from zero, irrespective of sign.) There’s no way to do that using built-in Excel functions, so I wrote my own — MAX_ABS and MIN_ABS — which required loading the data range into an array and stepping through it. MAX_ABS was pretty simple but MIN_ABS was somewhat trickier.

Interesting. From your comment, I can tell that you are many miles ahead of me in your spreadsheet program knowledge and understanding. 🙂 (I use LibreOffice’s Calc, but I think most things are probably comparable.) The thing I like best about your comment is that Excel makes it possible for you to create your own functions as necessary — in my mind, that kind of flexibility in a piece of software adds a lot of value.

Thank you for your help!

Thanks for including us in your article David. I’m glad we found one another – I’m enjoying all your TIPS insights. Jennifer

Thanks to you as well, Jennifer, for the time and effort you put into your video on this. I actually found it helpful to see multiple ways of explaining the calculations, the basis for them, and slightly different formulas to reach the same result. That all helped me figure out the ‘guiding principles’ well enough to be able to create / tweak my own set of formulas that work for me.

👍 Glad you’re sorted Dustin – it’s a great joy in life when your spreadsheet works out the way you want it to (for us anyway)!

Thank you to David for introducing me/us to Jennifer Lammer! I always look forward to settling in and watching Diamond Nestegg’s YouTube videos and have found them to be super helpful.

At least for my account, and even for the calculator example you show, Treasury Direct doesn’t round to the nearest whole dollar when they report the current value. (All of my current values reported show cents.) When I checked some of your $10K bond examples though, the math for those always worked out to an answer that was a whole number (so no need to show cents). So maybe that’s why you think they do that rounding? Or am I missing something?

It is, however, important to make sure that you round the final amount you’ve calculated off of the $25 base-line before you go on to use that amount in any following calculations; otherwise, you’ll sometimes be off by a few cents here and there. If using a spreadsheet program like Excel, this can be accomplished within a formula by using the ‘ROUND’ function.

Excellent point, and this should have been obvious to me, because my Savings Bond Calculator amounts did show cents, which I proceeded to eliminate by multiplying by 10. You are correct that the rounding takes place on the $25 baseline, to the nearest cent. Thanks for noticing this and I am correcting my article. (All the calculations remain the same, fortunately, since I was already rounding the $25 baseline number). For a $10,000 I Bond, there will never be cents in the final result, because of the factor of multiplying by 400. But that isn’t true for a $1,000 I Bond, with a factor of 40.

You’re welcome. I found it very helpful to work my through your article as well as the linked video (two different angles to the same thing), and that was the only thing that didn’t make sense to me. The time and effort you put into this post to help us all understand how the calculations are done is much appreciated.

*work my way through

Rounding the interest on a $25 bond to two decimal places was the first thing I did before multiplying by 400. There are no fractional pennies.

Thank you for mentioning that ROUND function because for the life of me I couldn’t match David’s Value numbers. Once I added ROUND, I could exactly match his figures.

You’re welcome, Jack. I had that same problem when I was trying to get my results to match the current values reported by Treasury Direct. It can be a bit of a mystery, especially if you have your formatting set to only show two decimal places because it definitely *looks* like it’s already rounded, but the unrounded amount is lurking in the shadows, waiting to pounce on you when you use that value in another calculation. 🙂 Luckily, I remembered encountering this effect in some other contexts. I had a memory that the ‘ROUND’ function existed, but hadn’t used it much, but it only took me a few minutes to research how it is meant to be used, and then was quite delighted to see that it was the final piece of the puzzle, at least for those of us that are interested in getting that exact match. 🙂

Imo, the Treasury Dept should be required, for purposes of clarity and transparency, to publicly post the sequence of formulas they use to arrive at their calculations. Other people should be able to duplicate their results. I can duplicate the interest on most of my other investments, my mortgage, etc. There’s nothing proprietary or top secret about it, so I don’t get why it isn’t publicly available.

Even when I try to duplicate the interest on very simple T-bills, I’m always off a few cents. In my view, I should be able to duplicate it exactly down to the penny. That is the simplest of simple formulas, and my result is always different from the Treasury’s result. It shouldn’t be.

If you use the equation provided by David and described by Jennifer then it works down to the penny. It’s actually pretty elegant albeit somewhat primitive (based on $25 bonds).

I suspect that the reason the Treasury Department isn’t more forthcoming about the equations is that the original computer code was probably written in COBOL, of which there are very few surviving practitioners, so no one at Treasury understands it. Your government at work.

Thanks for the info and the link to Jennifer’s video. The pseudo-compounding equation with the fractional exponent actually simplified my spreadsheet. More importantly, my numbers are now completely accurate.

I’m a math geek and played around in Excel to make it spit out the answer for me so that I can play a “what if” scenario (ie what if the next interest rate is 7.0% APR?)

All I did was take the original amount per six months (ie $10,000 to begin with, $10,356 the next six months etc), multiply it by the APR and divide by six to give me a monthly amount.

So… ($10,000 x 3.56%)/6 = $59.33 per month. Next interest will be ($10,356 x 4.81%)/6 = $83.02 per month

So not exact, but is off no more than $1 or less. First month they gave me $60 as opposed to $59.33, but only gave $296 as opposed to my $296.67 calculation. I know they only go by the dollars and not cents, but I was surprised that they rounded up to $60 the first month and down the others. I didn’t lose sleep over it.

That’s the way I’ve been doing it anyway.

Thanks for the article David. When I use the Treasury Direct calculator, I input my electronic bonds by putting TyDirect in the serial number space to generate a report for all of my savings bonds, so you can bend them in there.

I also use the calculator this way, to list all my I Bonds (and a couple remnant EE Bonds). When I create a new listing, I use the serial number space to note the fixed rate, such as 0.0% or 0.2%. And for my account (versus my wife’s) I make it DAVID: 0.0%. The calculator is a great way to see your total value, and yes, it works fine for both paper and electronic I Bonds. I wrote a guide back in 2018: https://seekingalpha.com/article/4179171-step-step-guide-to-using-treasurys-new-savings-bond-calculator

Pingback: Don’t go ballistic over the way TreasuryDirect reports I Bond interest | Treasury Inflation-Protected Securities

So is it safe to say that if I have a 10k I bond and it pays 9.62 for 12 months that at the end of the term I should have in my account 10,962 minus about 240 for the prior three months for a total of 10,722 give or take a few dollars. And if I cut that figure in half thats how much my account should have in 3 months. Does this work or no?

To simplify this, you could just take the base inflation rate for the 9.62%, which was 4.81%, so you’d earn $481 in six months and one half of that is $240.50. That is going to be close. But unfortunately, the correct answer is $236. That is the effect of pseudo-compounding, which causes the return to build over the six months. After the full six months you’d have $480, and that is still off by $1 because of rounding. Isn’t this fun?

So to me thats a heck of alot easier than attempting to figure out that other math. And going in you realize that the next 6 months would produce those extra few bucks I mean what more could you ask for right.

“Two trains are on same track and they are coming toward each other. The speed of the first train is 50 mph and the speed of the second train is 70 mph…”