Bogleheads.org

#Cruncher

When does Treasury Direct announce new iBond rates? TreasuryDirect will announce three I Bond rates on May 1st. [1] Fixed rate : applies for the life of the bond. Inflation rate: increase in the CPI over the six months from September 2022 to March 2023. Composite rate : the annual rate I Bonds purchased May through October will earn during their first six months. It combines the fixed and inflation rates. However, the inflation rate will be known before May 1st. On April 12 the Bureau of Labor Statistics will release the March CPI. Anyone can then compute the inflation rate by looking at this table and determining March's increase over September's 296.808. It will be combined not only with the fixed rate announced May 1st but also with the...

#Cruncher

Is anyone aware of a reliable calculator to easily calculate the amortization amounts for relevant periods of time? Yes, I bought both the 02/15/40 and 02/15/41 bonds and those are the ones for which I was expecting a premium amortization number. Here's an Excel calculator illustrated with $10,000 face value of the Feb 2040 purchased 12/9/2022 at a price of 111.28125. It assumes premium is amortized through the 2nd interest payment date, August 15th, every year until maturity on February 15th 2040. Using the initial yield-to-maturity (calculated with the Excel YIELD function), it calculates what the price would be on the given date with Excel's PRICE function. It then applies that price against the original inflation-indexed principal. The...

#Cruncher

Can you help me understand the 'discount rate' in your chart? The "Alive" sheet of my my Longevity Estimator Excel workbook calculates the survival-weighted benefit every year. The discount rate is then applied to each of these yearly values to get a present value. For example, the PV of $100 in one year at 5% is $95.24 = 100 / 1.05; the PV of $100 in two years is $90.70 = 100 / 1.05 ^ 2; etc. The discount rate can also be considered as the rate money would grow if invested instead of being spent. I can't seem to figure out if the increased monthly payment of the 50% option, if saved not spent each month, over 20 years until the term life insurance is over and say I pass away at year 21 (just after the term ends so no payout) is ...

#Cruncher

What if your wife dies first? Are you willing to live on 50% benefit? The original poster should check to see if this how the pension works; i.e., the 50% benefit kicks in when either spouse dies. With many pension plans the smaller benefit only kicks in when the primary pensioner dies. I evaluated both cases below with my Longevity Estimator Excel workbook. Here are the present values of the survival-weighted benefits for a man/woman couple both age 60 using the SSA 1960 Cohort Life Table . ------- Alive ------ Only Only --- Discount Rate --- Husb Wife Both 4% 5% 6% --- Benefit / Mo -- --- Present Value --- 100% 5,180 5,180 5,180 1,059K 946K 852K 75%-a 4,028 4,028 5,370 1,016K 912K 826K 50%-a 3,008 3,008 6,015 1,046K 946K 861K 75%-b 5,370...

#Cruncher

I bought some TIPS Bonds on 12/09/2022 at premiums to par ... and was expecting to see Acquisition Premium amortization amounts in box 6 of my 2022 1099-OID. However, the 1099-OID has zeros in box 6. I don't know exactly what box 6 on a 1099-OID is intended for, but I don't think it has anything to do with bond premium amortization for a TIPS. It is reported in box 12, "Bond Premium on Treasury Obligations" of a 1099- INT . That is where TreasuryDirect reports it for a $1,000 TIPS I purchased for educational purposes. This mention of OID got me to wondering. The premium to be amortized should refer to the unadjusted price, not the adjusted price including the index ratio. Only the 30-year TIPS maturing 2040 and later pay interest...

#Cruncher

... If you have 27,500 regular income & 62,000 SS you should only include 1/2 of SS to find what is provisional income. This is correct as explained in the Wiki's Taxation of Social Security benefits . (What you call "provisional income", it calls "relevant income".) If you[r] provisional income is ... Between 32,000 & 44,000 = 50% of SS is taxable Above 44,000 provisional income = 85% SS is taxable But you go astray here. Between 32,000 and 44,000 of relevant income (on a joint return), 50% of the excess over 32,000 is taxable -- up to a maximum 50% of total SS. And above 44,000 an additional 85% of the excess over 44,000 is taxable -- up to a maximum 85% of total SS. The following table shows this for the orig...

#Cruncher

2024 is indeed a leap year, which is why 366 days are used in the bill calculations. Treasury uses a different yield formula for notes and bonds. The YIELD function currently returns yields that agree with Treasury published yields for notes and bonds, but not for bills. I don't think Treasury has a yield formula for notes and bonds. In a note or bond auction, yield-to-maturity is the independent variable (it's what the competitive participants bid) and doesn't need to be calculated. The Treasury does have a price formula to convert the accepted yield into the corresponding price. You're correct that the YIELD function doesn't correctly calculate the yield (i.e., "Investment Rate" or "Coupon Equivalent Yield") for bills...

#Cruncher

[2] Accrued interest which you paid on purchase should be entered as an adjustment when entering a 1099-INT Is this contra-interest income adjustment applied in the year in which the accrued interest was paid, even if the related interest income is not received until the following tax year? This has been asked in other threads. Most members answer that it shouldn't be reported until the year of the first interest payment and cite an IRS instruction to that effect. However, I've always reported it for the year of purchase, for the following reasons (rationalizations?). If I didn't report it for the same year, I might forget it. I think the IRS rule is questionable since a cash-basis taxpayer is generally allowed to deduct expenses (as oppos...

#Cruncher

At the very short end, Tips become nominals, essentially. I think Kevin M had a thread section on this. ... I'd guess maybe this gets into the question being asked here. Jan 2023 TIPS: we now know exactly what the value will be at maturity Likewise, with today's release of the February CPI report, we know exactly the nominal redemption value of the TIPS that matures on 4/15/2023. Row 15 of the following table shows how it can have a positive nominal return even while its real return is negative. Row Col A Col B Col C Col D Formula in Column B 2 Face value 1,000 3 Settlement 3/15/2023 4 Matures 4/15/2023 5 Coupon 0.625% 6 Price 100.3125 =100+10/32 7 Previous interest 10/15/2022 =COUPPCD(B3,B4,2,1) 8 Days in period 182 =B4-B7 9 Days before s...

#Cruncher

I'm basically trying to figure out what a purchase before May will get for the next 12 months. A purchase before May will earn 6 months at the current 6.89% annual composite and then 6 more months at the new annual composite rate which will depend on the semi-annual inflation rate September 2022 - March 2023. For illustration assume it is 1.5%. Then the new composite rate would be 3.41% = 0.03406 = 0.004 + 2 * 0.015 + 0.004 * 0.015 , where "0.004" is the current 0.4% fixed rate. After the first six months a $25 I Bond would be worth 25.86 = 25 * (1 + 0.0689 / 2) (see this web page ) and then after the 2nd six months 26.30 = 25.86 * (1 + 0.0341 / 2) , an increase of 5.2% = 26.30 / 25 - 1 But if it's sold after 12 months we lose th...

#Cruncher

Why is a zero coupon 6 year rolled every year a good proxy for BND, which holds a ton of bonds? Because its (Macaulay) duration of 6 isn't too far from BND 's average duration of 6.6. If we want a simulation closer to BND, let's assume nine zero-coupon bonds with maturities of 2, 3, ..., 9, and 10, which averages 6. (Again we assume they are rolled over every year into bonds of the same term.) Columns M and N below show that the cumulative change of the nine bonds is fairly close to that of the single 6-year bond (column N is the same as column E in the first table of my previous post ). Row Col A Col B Col C Col D Col E Col F Col G Col H Col I Col J Col K Col L Col M Col N 2 Final rate 14.0% 3 After years 6 4 Change / yr +1.6% -----------...

#Cruncher

Numbercruncher, I'm lost on what you are trying to show [in this post ]. Can you summarize it in narrative English, please? skeptical showed in this post how federal income tax only kicks in for qualified dividends when they reach the start of the 15% tax bracket. He was looking at the case where no equities are sold and therefore without any capital gains. My analysis, on the other hand, assumes that all the equities are sold over a 30-year period to fund (along with dividends) your desired LMP. This will generate capital gains [1] (assumed all long term) which are subject to the same tax rate as dividends. Here is how it works for the first year with the assumptions in the table from my post : $194,070 is withdrawn from the portfolio. ($...

#Cruncher

... If you are married, you only need $8M with a 2.5% SWR for a 50/50 portfolio, after tax, as there would be no federal tax. ... How can there possibly be no federal tax? Lets take a $10M 50/50 portfolio, $5M VTI and $5M VTEB (Vanguard national muni): VTI produces $80K a year of mostly qualified dividends VTEB produces almost $150K a year of tax exempt interest 0% bracket is $82K, there is $26K married exemption , so you even have $30K to play with for roth conversions, etc, ... $230K income, no federal taxes. ...(underline added) Good point, skeptical, that federal tax on qualified dividends is only applied on the excess of adjusted gross income over the 15% capital gain bracket plus deductions "threshold". I've modified the an...

#Cruncher

The SEC yield on BND is 4.4% today. ... if the yield on intermediate bonds slowly ground upwards over the next 6 years to 14%? ... Would BND have a negative return for every single year of the next 6 years? Would BND have a positive return, but just lower than the 4.4% current yield? Something else? I chose 6 years because that is duration of the fund. It's quite complicated to model an actual bond fund. But I've modeled a hypothetical one similar to BND . It holds a single 6-year zero coupon bond that it rolls over every year into a new one. For this hypothetical case: It would have a negative return for only the first three years. It would have a slightly positive annual return of 0.7% over the first 6 years. Row Col A Col B Col C Col D ...

#Cruncher

Now I'm wondering about Excel's tbillyield function for the T-bills I just bought. ... I'm guessing the problem is either Excel or my understanding, ... Avoid the TBILLYIELD function. As shown in the bottom row of the table below, it computes neither the Discount Rate (aka High Rate) nor the Investment Rate (aka Coupon Equivalent Yield). [1] Instead use the TBILLEQ , YIELD , or YIELDDISC function for an approximation of the Investment Rate. Use YIELD with freq parameter = 1 for maturities of 26 weeks or less, and 2 for longer (i.e., 52 weeks); ... This will give results that match to at least 3 decimal places , which is all Treasury publishes. (underline added) Unfortunately this is no longer true for T-Bills issued March 1st or later. The...

#Cruncher

I purchased $13k and $12905 was deducted, ... The APY is (13000-12905)/12905*6 = 4.4% According to the auction results PDF the price was 99.275889. So, to be precise, you were charged $12,905.87 = 13000 * (99.275889 / 100). You should use the Investment Rate not the High Rate. And as pointed out above, use days not months in your calculations. Try this… (13000-12905)/12905*(365/56)X100 365 is used in this case I believe. 366 is used when the bond period spans a leap day ; I think. (underline added) Even with the precise price, your formula doesn't quite produce the 4.767% Investment Rate (aka Coupon Equivalen Yield) shown on the auction results PDF : 4.754% = (13000 / 12905.87 - 1) * (365 / 56) [*] 366 should be used in this case since tha...

#Cruncher

For tax with TIPS when one has to adjust the Box 8 (OID on Treasury Obligations) does one use the Accrued Interest or Accrued Inflation Compensation figures provided when the bond was issued? There should be no need to "adjust" the amount reported in box 8 of a 1099-OID. TurboTax -- and I assume other tax prep software -- will automatically eliminate it [1] when preparing your state return. [2] Accrued interest which you paid on purchase should be entered as an adjustment when entering a 1099- INT . I don't know what you mean by "Accrued Inflation Compensation". If you wish to check the amount reported in box 8, here is how it is supposed to be calculated for TIPS purchased during 2022: Multiply the face value by the ch...

#Cruncher

This is the way I look at this. In 7 years between 58 and 65, you will get $81,080 Delta between taking payout starting 58 and 65 is ~ $6240 You need to get to 78 years of age to break even. ... This is correct if we assume a 0% growth rate. (Since she will likely be the last to die, it's probably more meaningful to say the breakeven occurs when the wife is age 65.) Using a method FractualFran presented in this 2020 post we can also compute a breakeven assuming non-zero growth rates. For example, if pension money is invested at 5% per year, the breakeven occurs when the wife is age 80.7. The following table uses the Excel FV and NPER functions to calculate this. Rows 10 and 11 confirm that the future values of taking early or late do indee...

#Cruncher

1. If the interest rates for the bonds in the fund make a single step change at a point in time, then after a period of time equal to the duration the value of the holding with dividends reinvested will be equal to what the value would have been had the interest rates remained unchanged. Here's an illustration: A fund initially holds $1 in a single zero coupon bond that matures in five years [ * ] and every year it is rolled over into a new one. The bond initially yields 3%. After a certain number of years, the yield changes to 4% and remains there. Column B in the following table shows the value of the fund in the year the yield increases from 3% to 4%. For example if the yield changes after 10 years it is 1.2930 = 1.03^9 * 1.03^5 / 1.04^...

#Cruncher

I have decided to start investing in TIPS thanks to this thread and all that you, KevinM, and others on this forum have done to help me understand how bonds, and their math, works. ... I am finally comfortable enough to move beyond I Bonds and nominal bonds. I'm happy to have helped. When looked at in nominal dollar terms, TIPS are indeed more complicated than regular bonds. But when looked at in real terms, I submit they are less complicated. Nisiprius has made this point several times. For example, People think that a TIPS has some mysterious inflation bet in it. But if you think in real dollars, which is what I think we should be doing, it is just the opposite. In real dollars, it is TIPS that are simple, and nominal bonds that have the...

#Cruncher

If I sell all my shares, ... will I be hit with a 15% LT Capital Gains Tax? I am Married, Filing Jointly, and the two of us bring in about $80k/year. With $80,000 of income and the standard deduction, you can realize up to $36,950 of long term capital gains in 2023 before owing any additional tax. Additional LTCG would be taxed at a 15% marginal rate. Floor: ord income 10% bracket - --------------> Floor: ord income 12% bracket 22,000 --------------> Floor: ord income 22% bracket 89,450 --------------> Floor: LTCG & QDI 0% bracket - --------------> Floor: LTCG & QDI 15% bracket 89,250 --------------> Non-SS Ordinary Income 80,000 80,000 80,000 LTCG & QDI - 36,950 37,950 <--- Adjusted gross income 80,000 116,950 117,950 Standard...

#Cruncher

If inflation is 3% ..., the tax on a TIPS yielding 1.6% would be based on 4.6%. In a 32% tax bracket, that tax is 1.65% (including 3.8% NIIT), so the after-tax yield is 2.95% . ... For an I-Bond yielding 3.4% over 20 years, accrued as 1.7% semiannually, $1 would grow to 1.017^40=$1.963. The tax on the .963 gain, if it is 22%, is .212, so the final value is $1.751. Now compute that as a semiannual rate by taking the 40th root to get 1.41%, and double to get an annual yield of 2.82% . We can make the analysis more precise by using the 22% tax rate for the TIPS final year. This will bump the after tax yield up to 3.02% , as shown on row 15 below. Row Col A Col B Col C 2 Years 20 3 Inflation 3.0% 4 Tax rate years 1-19 35.8% 5 Tax rate year 20 ...

#Cruncher

watchnerd wrote: ↑Wed Mar 01, 2023 11:38 pmWith the Jan 2023 10 Year TIPS CUSIP 91282CGK1 priced at 1.55% YTM, ...

Prokofiev wrote: ↑Thu Mar 02, 2023 12:34 amDo you mean Jan 2033?

Yes. Bonds are usually described with their maturity date, not original issuance date.

Prokofiev, continuing in same post, wrote:That is at 1.69%

Are you referring to the 1.692% yield reported in Wednesday's WSJ TIPS Quotes? It is wrong. The calculated YIELD is about 0.1% points less:

Code: Select all

1.581% = YIELD(DATE(2023,3,2), DATE(2033,1,15), 1.125%, 95+27/32, 100, 2, 1) : using bid price
1.564% = YIELD(DATE(2023,3,2), DATE(2033,1,15), 1.125%, 96,       100, 2, 1) : using ask price

The current Bloomberg quote is about 1.58%.

#Cruncher

Using #Cruncher's first table, I can now calculate the 30-year withdrawal rate for any tax rate and inflation rate. Assuming 37% + 3.8% NIIT = 40.8% marginal tax rate, and a 17% inflation rate, the 30-year withdrawal rate is 1.4%, which is $140,000 on $10M. Here is a continuation of this analysis. It is a table of withdrawal rates for all the combinations of 5 specified tax rates from 0% to 40.8% and 18 inflation rates from 0% to 17%. It calculates the same way as Kevin except it assumes withdrawals are made at the start of each year instead of at the end. Row Col A Col B Col C Col D Col E Col F 2 TIPS yield 1.50% 3 Years 30 4 ------------- Tax Rate -------------- 5 0.0% 12.0% 23.8% 32.0% 40.8% 6 Inflation ---------- Withdrawal Rate ------...

#Cruncher

Using the #Cruncher 8% inflation scenario with a -1.35% after-tax return for TIPS [from this post ], here are the number of years the portfolio would last at different withdrawal rates: ... I believe we can consider this an after-tax, after-inflation withdrawal rate, so the annual withdrawal will increase each year in nominal terms. #Cruncher can correct me if I'm wrong about this. If this calculation is correct, a portfolio of 100% TIPS would last about 30 years at a 2.7% withdrawal rate. You are correct, Kevin. Here is a table with nominal dollar amounts that confirms your 2.7% withdrawal rate. Scroll to the bottom to see that the portfolio lasts exactly 30 years. Withdrawals are assumed made at the start of each year. Row Col A Col B Co...

#Cruncher

I think it would be good to see an actual simulation, comparing the after-tax returns of a TIPS and a nominal bought at same time with same maturity , ... (underline added) The comparison is the same regardless of maturity. Assuming a 32% tax rate, 3.9% regular Treasury yield , and 1.5% TIPS yield, the following table shows the after tax return for various inflation rates. For example, if it averages 5% per year, the TIPS has a -0.50% real return; but this is much better than the -2.24% real return for the regular Treasury. Both the regular Treasury and the TIPS do worse with higher inflation, but the TIPS is hurt less. Row Col A Col B Col C Col D 2 Tax Rate 32% 3 Treasury TIPS Diff 4 Inflat/Yield-> 3.90% 1.50% -2.40% 5 0% 2.65% 1.02% -1.6...

#Cruncher

Congratulations on creating a neat and simple to use tool , kaesler. It has the following advantages compared to my TIPS Ladder Builder Excel workbook: Automatically looks up prices and yields from WSJ TIPS Quotes . With my spreadsheet it's necessary to copy and paste. Automatically ignores old issues and adds new ones. I have to do this manually with my spreadsheet and it's cumbersome. I've just finished adding the January 2033 10-year and February 2053 30-year. Its interview type interface is easy to grasp. In contrast the main "Ladder" sheet in my spreadsheet has 23 columns A - W. (Not counting helper columns X - BG that are hidden by default. :wink: ) It has the following disadvantages compared to my workbook: Unable to manual...

#Cruncher

APR = ((25,000 - 24,776.39)/24,776.39) * (365/74)/100 = 4.4516% I believe that's an accurate calculation, but please let me know if you think not. That’s the formula I use but I think you made a typo at the end as it should be * 100 not / 100 Actually the formula shouldn't have either "/100" or "* 100". "4.4516%" or "4.4516/100" are simply alternate ways to express "0.044516". 4.4516% = 4.4516/100 = 0.044516 = ((25,000 - 24,776.39)/24,776.39) * (365/74) Also you can save yourself entering "24776.39" twice. Just use the equivalent formula: 4.4516% = (25000 / 24776.39 - 1) * (365 / 74) Settlement for Treasuries always is the next trading day. True for Treasuries traded in the second...

#Cruncher

So if I wanted to compare the T bill's rate to the APY of a CD, would I use the High Rate, the Investment Rate, or some other calculated value? You won't be far off to compare against the Investment Rate as MGBMartin says above. But a slightly better comparison would be against the Treasury Bill's APY (Annual Percentage Yield). It will be slightly higher than the Investment Rate, and can be calculated off the bill's price. Using the example of the 13-week bill issued 9/15/2022 with a 3.142% Investment Rate, the APY is 3.179% calculated as follows: APY = (100 / price) ^ (365 / days) - 1 3.179% = (100 / 99.222708) ^ (365 / 91) - 1 Also be aware that interest from CDs is subject to state income tax while interest from T-Bills is not. See Kevi...

#Cruncher

Mr. Potter wrote: ↑Tue Feb 14, 2023 5:06 am... the latest 6 month T-Bill is paying 5.03% APY.

To be picky, 5.03% is the "Investment Rate" for the 26-week (182-day) bill auctioned yesterday. The "APY" (Annual Percentage Yield) is 5.09%. Both are calculated off the price, but with slightly different formulas. (The Price and Investment Rate -- but not the APY -- are reported on the auction results PDF.)

Code: Select all

5.03% = (100 / 97.553111 - 1) * (365 / 182)
5.09% = (100 / 97.553111)     ^ (365 / 182) - 1

Investment Rate (aka Coupon Equivalent Yield) is the one generally reported for bonds. APY is the one generally reported for CDs and savings accounts.

#Cruncher

This Thursday the Treasury will auction a 30-year Treasury Inflation Protected Security (TIPS) to mature February 15, 2053. It should yield in the neighborhood of 1.5% as Friday's WSJ TIPS Quotes shows the TIPS maturing a year earlier on 2/15/2052 yielding 1.517%. Since this is an initial auction and not a re-opening of a previously issued TIPS, the coupon will be determined by the yield accepted at the auction. It will be set in 1/8% increments at or below the auction yield. This will cause the price to be at or slightly below par (i.e., 100). For anyone planning to participate, the table below gives an idea of your cost depending on the auction yield. Rows 13 to 25 illustrate that $10,000 face value would have a total cost (including infl...

#Cruncher

2. You might find this free calculator useful re the torpedo: https://www.covisum.com/resources/taxable-social-security-calculator The following calculator instruction is wrong. Enter taxable income excluding SS benefits (IRS Form 1040 lines 1, 2a, 2b,3a,3b,4b,5b,7,8) "Tax-exempt interest" (2a) should indeed be included, so it's misleading to say "Enter taxable income". "Ordinary dividends" (3b) includes "Qualified dividends" (3a). Therefore 3a should not be added in. I actually spent over 4 hours today with the SS Benefits worksheet ... My SS is only 25,225. ... if our non social security income was 49,553 or less, we would pay ever so slightly less than the 85% of the full SS ... When I saw your li...

#Cruncher

I'm investigating [E-Trade] as a possible broker. Here's an experience that makes me wary of trading T-Bills at E-Trade. It may be of interest to anyone contemplating manually rolling over T-bills there. (I've changed the $ amounts.) 9/28/2022: Bought $2,000 of 17-week bill issued 10/4/2022 and maturing 1/31/2023. 1/24/2023: Went online and attempted to order $3,000 of the 17-week bill to be auctioned 1/25/2023 and issued 1/31/2023 (maturity date of the old bill). At the time I had $1,500 of cash in the account. I got an error message that the order couldn't be placed because I had insufficient funds. I expected this because back in 2022 the bond desk told me that, not only didn't E-Trade offer T-bill auto-roll, but that I couldn't manuall...

#Cruncher

There were three original auctions of TIPS during 2022 where the unadjusted auction price was less than par. Here is a table of what the OID and Interest for each during 2022 per $1,000 of par should be reported ... OID per Int per Term Dated $1,000 par $1,000 par 30-yr 02-15 $70.54 $0.66 10-yr 07-15 $25.69 5-yr 10-15 $6.02 Your three OID amounts are slightly off, FactualFran. The OID for each TIPS purchased during 2022 will equal its face value times its index ratio increase from the issue (or settlement) date until 1/1/2023. [1] The far right column in the table below of the five original TIPS auctions in 2022 shows this for $1,000 of face value. [2] [3] You seem to be basing off the index ratio on the Dated date (which is always 1.0), r...

#Cruncher

The disclosures page says 4.89% To be precise, the Interest Rates and Annual Percentage Yields (APY) section of Capital One's Certificate of Deposit Disclosure says 4.8889% is the "Interest Rate". This is the Annual Percentage Rate (APR) that is equivalent to the 5% Annual Percentage Yield (APY). It is twelve times the monthly rate that, when compounded over a year, would produce a 5% increase. For example, the following shows how a $10,000 deposit will grow to $10,457.40 after the eleven month term, and to $10,500 if it continued to grow for one more month at that rate. [*] Row Col A Col B Formulas 2 APY 5.0000% 3 APR 4.8889% =12*((1+B2)^(1/12)-1) 4 Rate/mo 0.4074% =B3/12 5 Month Balance 6 0 10,000.00 7 1 10,040.74 =B6*(1+B$4) 8...

#Cruncher

Rows 14 - 17 below show the cash flow corresponding to the explanations toddthebod and lightning have provided above. Row Col A Col B Formulas in Column B 2 Face value 50,000 3 Settlement 2/07/2023 4 Mature 4/30/2024 5 Coupon 2.500% 6 Price 97.242 7 Yield to maturity 4.840% =YIELD(B3,B4,B5,B6,100,2,1) 8 Prev coupon date 10/31/2022 =IF(DAY(B4+1)=1,COUPPCD(B3+1,B4+1,2,1)-1,COUPPCD(B3,B4,2,1)) 9 Next coupon date 4/30/2023 =IF(DAY(B4+1)=1,COUPNCD(B3+1,B4+1,2,1)-1,COUPNCD(B3,B4,2,1)) 10 Days before settle 99 =B3-B8 11 Days in period 181 =B9-B8 12 Accrued interest 341.85 =B2*(B5/2)*(B10/B11) 13 Date Cash Flow 14 2/07/2023 -48,962.85 =-B2*(B6/100)-B12 15 4/30/2023 625.00 =B$2*(B$5/2) 16 10/31/2023 625.00 =B$2*(B$5/2) 17 4/30/2024 50,625.00 =B$2*(B...

#Cruncher

stefanich wrote: ↑Thu Feb 02, 2023 6:42 pmWhen I updated/pasted the WSJ tab I noticed that there was a recent new issue 10-yr TIPS issue maturing in Jan 2033. But it doesn't appear in the Ladder. Is there something additionally I need to due to make that happen ... ?

You could add it yourself to the "Ladder" sheet. But this is complicated. (See item 3 under "Miscellaneous" on the "Instructions" sheet.) I suggest you just wait a couple of weeks. I'll be adding the Jan 2033 10-year at the same time as I add the Feb 2053 30-year the weekend after its auction later this month.

#Cruncher

Manual rollover at Schwab worked for me with no days uninvested. I had an 8-week bill maturing 11/29/2022. I was able to buy a new 8-week at the 11/23/2022 auction to be issued 11/29. Without the proceeds of the old bill maturing, there weren't enough funds to cover the new purchase. (It's a cash, not margin, account.) But apparently Schwab's online system was smart enough to know that there would be sufficient funds on the settlement date; and the order went through. I did not choose auto-roll when I ordered the old purchase at the 9/29/2022 auction . But, as a test, I did choose it when I ordered the new purchase. I'll be disappointed if it skips a week after maturity as happened to Joe. If so, I'll turn off auto-roll and go back to plac...

#Cruncher

8301 wrote: ↑Wed Feb 01, 2023 5:00 pmI bought some 17-week T-bills from today's auction. High rate is 4.625% and investment rate is 4.762%. I don't know what the equivalent APY is.

The Annual Percentage Yield (APY) can be calculated from the price with a slightly different formula than the one that calculates the Investment Rate. The price, if not known, can be first calculated from the discount rate (i.e., the High Rate at auction). APY is generally how bank savings accounts and CDs quote their interest rate.

Code: Select all

  Discount rate      4.625%
          Price   98.471181  =  100 * (1 - 0.04625 * (119/360))
Investment rate      4.762%  = (100/98.471181 - 1) * (365/119)
            APY      4.839%  = (100/98.471181)     ^ (365/119) - 1

#Cruncher

stefanich wrote: ↑Wed Feb 01, 2023 2:07 pm D’oh! I entirely misunderstood what Other Interest reflects. I was thinking it was applicable to the (non-selected) bonds in that respected row. Now I understand. Thanks for the clarification.

No need for the forehead slap. The layout of the "Ladder" sheet is at fault. However, I don't know any clean way to fix it.

I want a column to show the interest each year for bonds maturing in later years. But since each row is for a specific bond, I just throw this other interest into the row for the latest bond maturing each year. So naturally it looks like that interest applies to that bond.

#Cruncher

Edited 03/17 03:35 PM. In a mirror image of the previous week, this week short term yields fell significantly, medium term yields moderately, and long term yields hardly moved at all. The yield of the 1+ index decreased 0.26% points to +1.44% . Edited 03/10 4:10 PM. This week short term yields rose significantly, medium term yields moderately, and long term yields hardly at all. The yield of the 1+ index increased 0.10% points to +1.70% . Edited 03/03 4:30 PM. Yields fell this week on all but the shortest maturities. The yield of the 1+ index fell 0.17% points to +1.60% . Because of its yield's day-to-day volatility so close to maturity, beginning 2/28 I removed the April 2023 maturity from the 0-5 index. Edited 02/24 4:50 PM. Yields rose ...

#Cruncher

Instead of 30 years, I am looking to create a shortened ladder of 25 years that begins in about four years. Per your instructions I have zeroed out the Multiplier value (column N) in the rows for the first four years and the last row (1952). But I noticed that the Other Interest column (W) remains populated for those five years and their respective amounts are included in the Principal + Interest amount total at the bottom of the column. Should those amounts also be deleted? I assume you mean "last row ( 20 52)", stefanich. :wink: The principal and interest displayed in columns U:W of the "Ladder" sheet are computed values and should not be deleted. Blanking out the Multiplier cells (column N) for the years before your ...

#Cruncher

... I am trying to see if buying this TIPS is better or worse than buying the TBILL: ... the TIPS ... YTM=2.286% while the TBILL ...YTM=4.658% ... You can't know exactly what you will get back on a TIPS. It depends on inflation. Tom_T is correct. The nominal return of the TIPS will depend on future inflation. Cell C13 in the table below shows that inflation of 0.851% would produce a 4.658% nominal return for the TIPS in the original post, the same as the T-Bill's return. Specifically this means that the "Reference CPI" would need to increase 0.851% from 3/1/2023, the latest date for which we know TIPS "index ratios", until 4/15/2023 when the TIPS matures. [1] The Ref CPI on 3/1/2023 is the same as the monthly CPI for De...

#Cruncher

Maybe TbillYield is actually calculating discount rate? Microsoft's description of TBILLYIELD says it "Returns the yield for a Treasury bill". But it actually returns a value closer to the Discount Rate. However, it doesn't even do that correctly. See my post, Re: Help me understand my T-Bill Auction Results for more. Row 8 in the table below shows the correct yield, aka "Investment Rate", using the formula from the Treasury's Price, Yield and Rate Calculations for a Treasury Bill . Rows 9-11 show how the TBILLEQ , YIELD , and YIELDDISC functions can also calculate this rate -- correct to 11 percentage places. Row 12 shows how the TBILLYIELD function returns a value closer to the Discount Rate (on row 7) than to the Inv...

#Cruncher

COUP[N]UM appears to count backwards from the maturity date, so COUPNUM(original_settlement,maturity,2) - COUPNUM(today,maturity,2) should equal the number of coupons already paid. Correct. This is confirmed below on rows 6-8 which use the 5-Year TIPS First Issued 10/30/2020 as an example. Rows 9-11 show how the COUPNCD and COUPPCD functions can also be used to get the same answer. Row Col A Col B Formulas in Column B 2 Originally issued 10/30/2020 3 Now 1/26/2023 4 Matures 10/15/2025 5 Coupons per year 2 6 Total coupons 10 =COUPNUM(B2,B4,B5,1) 7 Remaining coupons 6 =COUPNUM(B3,B4,B5,1) 8 Coupons already paid 4 =B6-B7 9 First coupon date 4/15/2021 =COUPNCD(B2,B4,B5,1) 10 Previous coupon date 10/15/2022 =COUPPCD(B3,B4,B5,1) 11 Coupons alrea...

#Cruncher

Me: Age 66 She: Age 67 ... The annuity option has multiple choices regarding COLAs and survivor benefits. For example, if I were to elect an annuity beginning 2/1/2023, a 0% COLA with 100% survivor would be $2861 monthly, with a 2% COLA $2311, and 5% COLA $1608. Using the SSA 1960 Cohort Life Table for both you and your wife, the "Alive" sheet of my Longevity Estimator shows the present value of the survival-weighted benefits to be slightly higher with the No COLA option with a 4%, 5% or 6% discount rate. ---- Discount Rate ---- Per Mo 0% 4% 5% 6% ------ ---- ---- ---- ---- No COLA $2,861 807K 513K 466K 426K 2% COLA $2,311 843K 510K 458K 414K 5% COLA $1,608 901K 502K 443K 393K Here's the detail from the "ToPaste" sheet ...

#Cruncher

Stinky wrote: ↑Tue Jan 17, 2023 9:56 am... an IRR of 3.85%. Solved using =IRR function in Excel.

I get the same result with the Excel RATE function:
3.85% = RATE(5, 112779, -504247, 0, 0)

#Cruncher

I would like to buy some ten year TIPS at the 1/19/22023 auction using Schwab but I am unsure how to estimate the amount of money needed in my settlement account to ensure that the transaction is completed. Your cost will be: index ratio X face value X ((price / 100) + (coupon / 2) X (days until issuance / days in interest period)) The index ratio is 0.99948 per the auction announcement . Since this is an initial auction and the yield will exceed 0.125%, the price will be par or slightly under. Say 100 to be conservative. The coupon will be set to the auction's yield or slightly under. Say 2% to be conservative. There are 16 days from 1/15/2023, the start of the 6-month interest period, until issuance on 1/31/2023; and 181 days in the inte...

#Cruncher

gogleheads.orb wrote: ↑Fri Jan 13, 2023 7:55 pm using a Net Present Value calculator It looks like you should defer. If you assume a 5% nominal return on the missed $45k you'll need to live to 68 to be ahead after deferring. If you assume 10% nominal return you need to live to 79.

Using the NPER function I get age 65 and 72, not 68 and 79:

Code: Select all

65.4 = NPER(0.05, 5641, -45056, 0, 0) + 55
71.8 = NPER(0.10, 5641, -45056, 0, 0) + 55

#Cruncher

Let's say that 364 days pass, so the first coupon payment is only 1 day away. Now, the present values of the cash flows look like this: Year Cash flows Present value 1 10 9.996 2 10 8.692 3 10 7.559 4 10 6.573 5 110 62.869 Total 95.688 The accrued interest would be $10 * (364 / 365) = 9.973. The Dirty Price would be: Clean Price = 9.996 + 8.692 + 7.559 + 6.573 + 62.869 Clean Price = 95.688 Accrued Interest = 9.973 Dirty Price = Clean Price + Accrued Interest Dirty Price = 105.661 95.688 is the "dirty price" and this is the total you pay. We subtract the 9.973 accrued interest to get a "clean price" of 85.715. This is easier to see for a $100 10% annual coupon bond trading at par. Let's look at the dirty and clean prices...

Search found 3673 matches