Re: Tool for calculating TIPS current value

rustwood wrote: Sat Aug 30, 2025 6:26 pm

Kevin M wrote: Sat Aug 30, 2025 3:59 pm

This tool is basically an index ratio lookup tool…

I’ve used tipsladder before, but only to generate purchase lists. I now see that the cash flow calendar download shows the actual remaining interest payments for the current year, albeit in real dollars. That’s fine for my needs since the real/nominal difference should be minimal over such a short time span. Thanks for the detailed reply.

Thanks.

Yes, in real dollars relative to the date the tool is used, so for the next TIPS coupon date of 10/15/2025, it will be quite close to the nominal dollar amount shown today by tipsladder.com. We can get even closer by using the index ratio for the last known ref CPI date, which is 10/1/2025. Although this index ratio can be calculated from the 10/1/2025 ref CPI and dated date ref CPI, index ratios are only published through the last day of the month before the last know ref CPI date, so 9/30/2025 in this case.

Both the tools use the current date index ratio to calculate the inflation adjusted principal and other related values.

It might be useful to walk through the arithmetic to calculate estimates of the nominal interest payment. For this, I’ll use a TIPS with an October interest payment, CUSIP 91282CKL4, dated date = 4/15/2024, coupon = 2.125%.

First let’s look at the inflation adjusted principal.

Using the tipsladder.com defaults, we would purchase 9 of these. As of today, both tools show the principal value for these as $9,385. tips-calculator.com shows the correct amount to two decimal places, $9384.57. I just discovered that the tipsladder.com download is not displaying the correct amount to two decimal places; it shows $9,384.60; the cause of this error is that the inflation adjusted principal is not being correctly rounded. Index ratios are rounded to five decimal places, so the inflation adjusted principal (per $1,000 face) should be rounded to two decimal places for subsequent calculations, but it is being rounded to 5 decimal places; rounding it to two decimal places gives the correct answer.

For 91282CKL4, the index ratio today is 1.04273, which is shown by tips-calculator.com in the INDEX RATIO HISTORY (suggestion: these values should be rounded to 5 decimal places). I typically calculate index ratios from the ref CPI for the date of interest (same for all TIPS) and the dated date ref CPI for the TIPS of interest, but index ratios can be retrieved directly from various sources. One way is to use the FiscalData API and the Google Sheets IMPORTDATA function with this URL:

https://api.fiscaldata.treasury.gov/services/api/fiscal_service/v1/accounting/od/tips_cpi_data_detail?fields=cusip,index_date,index_ratio,ref_cpi&filter=index_date:gte:2025-08-28,index_date:lte:2030-01-01,cusip:eq:91282CKL4&sort=index_date,cusip&format=csv&page[size]=10000

(The tips calculator tool uses the TreasuryDirect API, but I discovered the FiscalData API a few months ago, and find it to be much more powerful.)

So the inflation indexed principal calculation is:

Face value * index ratio = 9,000 * 1.04273 = 9,384.57

Similarly, using today’s index ratio, the interest payment would be:

Face value * coupon / 2 * index ratio = 9,000 * 2.125% / 2 * 1.04273 = 99.71

This is what tipsladder.com shows:

The latest index ratio, for 9/30/2025, shown by the tool or obtained with the Google Sheets formula above, is 1.04437. Substituting this IR into the equation above, we get a coupon payment estimate of 99.87.

As mentioned, we can calculate the ref CPI for 10/1/2025 as follows:

latest known ref CPI / dated date ref CPI = 323.048 / 309.30787 = 1.04442

Using this index ratio, the coupon payment estimate still is 99.87 rounded to 2 decimal places.

The last known ref CPI is just the most recent CPI all urban consumers, not seasonally adjusted value, which is easy to get. I get it from FRED with this google sheets formula:

=importdata("https://fred.stlouisfed.org/graph/fredgraph.csv?id=CPIAUCNS&cosd="&$B$8)

, where the desired observation start date is in cell B8.

The dated date ref CPI values for all outstanding TIPS can be retrieved with the FiscalData API using this google sheets formula:

=IMPORTDATA("https://api.fiscaldata.treasury.gov/services/api/fiscal_service/v1/accounting/od/tips_cpi_data_summary?fields="&TEXTJOIN(",",TRUE,I2:I)&"&sort=maturity_date&format=csv",",")

, where the desired fields are in cells I2:I; the only fields required for this purpose are cusip and ref_cpi_on_dated_date, although for purposes of this exercise I also pull dated_date, maturity_date, and interest_rate.

For anyone who’s interested in digging into this more deeply with google sheets, I’ve shared a spreadsheet that demonstrates all of this and much more. The original purpose of this spreadsheet was to explore TIPS seasonal adjustment methods, but since that requires access to ref CPI values, the formulas discussed above are in supporting sheets; e.g., TIPS ref, CPI, RefCPI, and TIPSindexRatios. Anyone with the link can load a read only copy, then make their own copy if they want to modify it. Here’s the link: TIPS seasonal adjustments (SA).