Excel formula for value of EE bond?

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
Topic Author
mmcmonster
Posts: 341
Joined: Sun Jan 12, 2014 1:18 pm

Excel formula for value of EE bond?

Post by mmcmonster » Sun Aug 11, 2019 7:06 pm

The background:
I just found almost 60 (paper) EE bonds issued between 7/2005 and 11/2011. They are of various denominations and are addressed to DW, DD, and DS. (My dad gave them as gifts over several years, instead of cash. I gotta say, they look beautiful.)

I don't want to convert them to electronic form at this point, particularly as I don't want to separate them into who owns them until the time that they are redeemed (20 years after issuance).

I've gone to https://www.treasurydirect.gov/BC/SBCPrice to get the current value of a few, but the idea that I have to save that .html page and reload it to update the valuation seems ... inelegant.

Is there a formula I can use in Excel (or, more accurately, Libreoffice Calc) so that I can get the current value of each of these bonds?

User avatar
fortfun
Posts: 2173
Joined: Tue Apr 19, 2016 7:31 pm

Re: Excel formula for value of EE bond?

Post by fortfun » Sun Aug 11, 2019 8:43 pm

mmcmonster wrote:
Sun Aug 11, 2019 7:06 pm
The background:
I just found almost 60 (paper) EE bonds issued between 7/2005 and 11/2011. They are of various denominations and are addressed to DW, DD, and DS. (My dad gave them as gifts over several years, instead of cash. I gotta say, they look beautiful.)

I don't want to convert them to electronic form at this point, particularly as I don't want to separate them into who owns them until the time that they are redeemed (20 years after issuance).

I've gone to https://www.treasurydirect.gov/BC/SBCPrice to get the current value of a few, but the idea that I have to save that .html page and reload it to update the valuation seems ... inelegant.

Is there a formula I can use in Excel (or, more accurately, Libreoffice Calc) so that I can get the current value of each of these bonds?
This might help (google spreadsheet):
https://support.google.com/docs/answer/3093339?hl=en

User avatar
jeffyscott
Posts: 8064
Joined: Tue Feb 27, 2007 9:12 am
Location: Wisconsin

Re: Excel formula for value of EE bond?

Post by jeffyscott » Mon Aug 12, 2019 9:06 am

mmcmonster wrote:
Sun Aug 11, 2019 7:06 pm
I've gone to https://www.treasurydirect.gov/BC/SBCPrice to get the current value of a few, but the idea that I have to save that .html page and reload it to update the valuation seems ... inelegant.
Saving there works just fine, though. I have been doing it that way for a long time (10-20 years?) with a few paper I-bonds. I put in the current date, click update, and it gives the current values and the current interest rates. Maybe that's more useful for I-bonds, where the interest rate varies, though.
Time is your friend; impulse is your enemy. - John C. Bogle

jmk
Posts: 480
Joined: Tue Nov 01, 2011 7:48 pm

Re: Excel formula for value of EE bond?

Post by jmk » Mon Aug 12, 2019 12:50 pm

mmcmonster wrote:
Sun Aug 11, 2019 7:06 pm
Is there a formula I can use in Excel (or, more accurately, Libreoffice Calc) so that I can get the current value of each of these bonds?
if you want the actual sell value at a moment, the above works. But if you're planning on holding till they double in 20 years, you might just want to alternatively use 3.52% and do a standard "=pv" excel calculation.

I do that in my asset allocation spreadsheet, but of course you have to realize this is the pv-of-fv, not the actual cash-in-hand price you'd get if you sold right now.
Last edited by jmk on Tue Aug 13, 2019 12:50 pm, edited 1 time in total.

FactualFran
Posts: 865
Joined: Sat Feb 21, 2015 2:29 pm

Re: Excel formula for value of EE bond?

Post by FactualFran » Mon Aug 12, 2019 2:43 pm

mmcmonster wrote:
Sun Aug 11, 2019 7:06 pm
The background:
I just found almost 60 (paper) EE bonds issued between 7/2005 and 11/2011. They are of various denominations and are addressed to DW, DD, and DS. (My dad gave them as gifts over several years, instead of cash. I gotta say, they look beautiful.)

I don't want to convert them to electronic form at this point, particularly as I don't want to separate them into who owns them until the time that they are redeemed (20 years after issuance).

I've gone to https://www.treasurydirect.gov/BC/SBCPrice to get the current value of a few, but the idea that I have to save that .html page and reload it to update the valuation seems ... inelegant.

Is there a formula I can use in Excel (or, more accurately, Libreoffice Calc) so that I can get the current value of each of these bonds?
Because all those EE bonds have a fixed yield, ignoring the early redemption penalties, it is relatively easy to set up a spreadsheet that calculates the redemption values at the end of each 6 month interval since the bond was issued. An important aspect to get correct is that the Treasury rounds the redemption value to the nearest cent for a paper savings bond with a face value of $25.00 that has an issue price of $12.50.

For example, an EE bond that was issued 7/2005 has a fixed yield of 3.50%. 6 months after being issued, a $25 face value bond would have a redemption value, ignoring the early-redemption penalty, of 12.5*(1+.035/2) = $12.72, rounded to the nearest cent. The redemption value on a paper bond with a face value of other than $25 is the redemption value of a $25 bond times the face value of the bond divided by $25. For example, a $100 bond issued 7/2005 would have a redemption value, ignoring the early-redemption penalty, of $12.72*100/25 = $50.88. The redemption value of a $25 bond issued 7/2005 after 12 months would be 12.72*(1+0.35/2) = $12.94.

In addition the complication of determining the early-redemption penalty, another complication is having the redemption value be at least its face value after 20 years. I don't know if you would find writing a spreadsheet that takes care of those complications more of less inelegant than saving a web page of the Savings Bond Calculator.

User avatar
jeffyscott
Posts: 8064
Joined: Tue Feb 27, 2007 9:12 am
Location: Wisconsin

Re: Excel formula for value of EE bond?

Post by jeffyscott » Tue Aug 13, 2019 7:33 am

jmk wrote:
Mon Aug 12, 2019 12:50 pm
But if you're planning on holding till they double in 20 years, you might just want to alternatively use 3.43% and do a standard "=pv" excel calculation.
I think it comes to more like 3.53%. But what I think might be more interesting and useful, would be a spreadsheet with the actual current value based on the 0.1% rate, or whatever it is, and the YTM. I don't have a spreadsheet, but that is what I have looked at a few times to tell me that the few that we have are clearly best kept until they mature.

They had about 12-13 years to go and last time I looked the YTM was above 5%. If value is something like $10,100 now and will be $20,000 in 12 years, that puts YTM at over 5.8%. So I don't feel a need to track this, as they and some old I-bonds would be the very last things that I would consider selling in the current interest rate environment.
Time is your friend; impulse is your enemy. - John C. Bogle

jmk
Posts: 480
Joined: Tue Nov 01, 2011 7:48 pm

Re: Excel formula for value of EE bond?

Post by jmk » Tue Aug 13, 2019 12:53 pm

jeffyscott wrote:
Tue Aug 13, 2019 7:33 am
jmk wrote:
Mon Aug 12, 2019 12:50 pm
But if you're planning on holding till they double in 20 years, you might just want to alternatively use 3.43% and do a standard "=pv" excel calculation.
I think it comes to more like 3.53%. But what I think might be more interesting and useful, would be a spreadsheet with the actual current value based on the 0.1% rate, or whatever it is, and the YTM. I don't have a spreadsheet, but that is what I have looked at a few times to tell me that the few that we have are clearly best kept until they mature.

They had about 12-13 years to go and last time I looked the YTM was above 5%. If value is something like $10,100 now and will be $20,000 in 12 years, that puts YTM at over 5.8%. So I don't feel a need to track this, as they and some old I-bonds would be the very last things that I would consider selling in the current interest rate environment.
Yes, 3.53% thanks for catching that. They're both valuable measures depending on context: to decide between keeping or investing elsewhere the ytm would be valuable, in order to get its current value for a buy-and-hold the pv(3.53%) would be valuable. So I'd want to know both.

User avatar
dratkinson
Posts: 4623
Joined: Thu Jul 26, 2007 6:23 pm
Location: Centennial CO

Re: Excel formula for value of EE bond?

Post by dratkinson » Tue Aug 13, 2019 1:20 pm

mmcmonster wrote:
Sun Aug 11, 2019 7:06 pm

I've gone to https://www.treasurydirect.gov/BC/SBCPrice to get the current value of a few, but the idea that I have to save that .html page and reload it to update the valuation seems ... inelegant.

Is there a formula I can use in Excel (or, more accurately, Libreoffice Calc) so that I can get the current value of each of these bonds?
It's only inelegant because you have not considered all of the work the SBC is doing for you.



In my case, when I used savings bonds (paper/electronic, EE/I), I tracked my paper savings bonds (EE/I) using SBC. It worked fine and 5-minutes/mo didn't seem to be too much work.

Once a month I'd open my SBC *.html file, punch the "Return to Savings Bond Calculator" button, and get the new redemption values. Then I'd copy/paste the data into Excel for my use, so didn't need to save an updated copy of *.html. (I only had to save a new version of SBC *.html after getting/entering new paper I bonds from playing the IRS paper savings bond tax refund game.)

If I were to attempt to track savings bond values directly in Excel, I'd need some way to find/track the changing interest rates (EE series: before/at/after maturity; I series: constant fixed rate/monthly inflation rate), and that would be much more trouble to find/implement (reinvent the SBC wheel) in Excel than using SBC.

:idea: 3-step update idea for paper savings bonds. I did this to make my life a little easier. On the Excel sheet that tracked my paper savings bond data, I created a hypertext link to my SBC *.html file on my HD. So my monthly steps became:
--Click Excel hypertext link to open SBC *.html file (in Firefox, IE,...).
--Click SBC *.html file "Return to Savings Bond Calculator" to get updated savings bond data.
--Copy/paste my updated SBC savings bond data into my Excel sheet.



In your case, if you only have EE bonds, and you knew they paid a monthly fixed interest rate until their maturity date (then doubled), then you could use the Excel Future Value (FV) function.
--Update EE redemption values as updated FV every month before the maturity month.
--Update redemption values as double the face value on/after the maturity month.
--Also seem to recall they earn a different/lower rate between maturity and redemption dates.
--Also remember 1yr-lockout and 3mo-penalty on redemptions (doesn't apply in your case, but they exist).

Your problems (periodic interest rate changes) will increase if you decide you want to get paper I bonds from playing the IRS tax refund game. (If that is still a thing.)

So lots of moving pieces (interest rate changes) to find/track. Or you could just let SBC take care of all of the minutia for you and copy/paste your updated data from SBC into Excel. (Worked for me.)



:idea: 1-step update idea for paper savings bonds. I recall a past topic in which was discussed a book, Automate the Boring Stuff. It talked about using Python script to work among applications. So you could set up a Python script link on your Excel savings bond sheet, that when clicked, would:
--Open your SBC *.html file (in Firefox, IE,...).
--Click the SBC file "Return to Savings Bond Calculator" button to get your updated savings bond data.
--Then copy/paste your SBC savings bond data into Excel.

So your tasks become:
--Monthly, open Excel, click the link to start your Python script to update your paper savings bond data.
--Annually, manually update your SBC *.html file after playing the IRS paper savings bond tax refund game.



:idea: 1-step update idea for all savings bonds. Use what you learn about automating the boring stuff to extend above Python script to also fetch your TD electronic savings bond updates into Excel.




Yes, the paper savings bonds were pretty and I enjoyed having them, and playing the IRS savings bond tax refund game made doing taxes less onerous.

But eventually decided (after owing/tracking them ~5yr) that they were a low-yield investment, that were a lot of work to get (local bank or IRS for paper bonds, TD for electronic bonds), track (SBC/TD monthly updates), redeem, and manage another account (TD for electronic). And they would be a problem for my heirs, and for me in my old age mental decline. And after I decided that I didn't want to buy any more, the next step was to realize that I didn't want to keep what I had. What to do?

So while still mentally capable, did my due diligence, redeemed all and bought a single-state municipal bond fund for its: higher yield, better tax benefits (fed+state), monthly dividends, no annual purchase limit, no 1yr redemption lockout, no 3mo redemption penalty, and simpler account to manage. Why?

I had decided that pretty paper and a treasury-guaranteed lower risk/reward/tax-benefit investment, did not trump a simpler and higher risk/reward/tax-benefit investment.



Your choice.
d.r.a, not dr.a. | I'm a novice investor, you are forewarned.

Post Reply