Code: Select all
Row Col A Col B Col C Col D Col E Col F Col G Col H Col I Col J Col K
1 Last rates 11/2020 (month latest rates were announced)
2 Future CPI 2.0%
3 Face Fixed Now Remain Comp Grows
4 Bought Value Rate Worth As of Matures Year Life Rate To Gain
------- ----- ----- ------ ------ ------- ---- ---- ----- ------ ------
5 11/1998 10,000 3.3% 33,952 5/2021 11/2028 2028 7.5 5.33% 50,373 40,373
6 5/1999 10,000 3.3% 33,104 5/2021 5/2029 2029 8.0 5.33% 50,424 40,424
7 11/1999 10,000 3.4% 32,936 5/2021 11/2029 2029 8.5 5.43% 51,933 41,933
8 2/2000 10,000 3.4% 32,936 8/2021 2/2030 2030 8.5 5.43% 51,933 41,933
9 8/2000 10,000 3.6% 33,148 8/2021 8/2030 2030 9.0 5.64% 54,683 44,683
10 2/2001 10,000 3.4% 30,716 8/2021 2/2031 2031 9.5 5.43% 51,099 41,099
------ ------- -------
11 Total 60,000 310,445 250,445
Code: Select all
Row Col A Col B Col C Col D Col E Col F Col G Col H Col I Col J Col K
12 10,000 2028 50,373 40,373
13 20,000 2029 102,357 82,357
14 20,000 2030 106,616 86,616
15 10,000 2031 51,099 41,099
------ ------- -------
16 Total 60,000 310,445 250,445
- Select All, Copy, and Paste [1] the following at cell A1 of a blank Excel sheet:
Code: Select all
Last rates 44136 Future CPI 0.02 Face Fixed Now Remain Comp Grows Bought Value Rate Worth As of Matures Year Life Rate To Gain 36100 10000 0.033 33952 =DATE(YEAR(B$1),MONTH(B$1)+6+MOD(MONTH(A5),6)-MOD(MONTH(B$1),6)+IF(MOD(MONTH(A5),6)<5,6,0),DAY(B$1)) =DATE(YEAR(A5)+30,MONTH(A5),DAY(A5)) =YEAR(F5) =YEAR(F5)-YEAR(E5)+(MONTH(F5)-MONTH(E5))/12 =ROUND(C5+B$2+C5*(B$2/2),4) =ROUND(D5*(1+I5/2)^(H5*2),0) =J5-B5
- Format for readability, especially cells B1, A5, E5, and F5 as dates.
- Use my I Bond Portfolio Calculator Excel workbook to determine the value of each of your I Bonds as of the latest month.
- Copy the values from the Portfolio Calculator into columns A, B, C, and D starting in row 5. [2]
- Copy the formulas in cells E5:K5 down to the bottom row.
- Calculate the totals by year using the Excel SUMIF function. For the example above, here it is in cell B12. It is copied to B13:B15 and J12:K15.
10,000 = SUMIF($G$5:$G$10, $G12, B$5:B$10)
- If you have trouble pasting, try "Paste Special" and "Text".
- Columns A:C can be pasted directly from the same columns in the Portfolio Calculator. But the values for column D should be pasted from the "latest month" in the Portfolio Calculator, which is the right-most column shown in black. This will probably not be the same column for every I Bond. Alternatively you can get the values for columns A:D one-by-one from my Growth of I Bond Value website if each I Bond is one of the nine denominations shown.