CODEWITHSUNDEEP
excelfinancebloomberg
Dj Mauch
Dj Mauch

Reputation: 23

Bloomberg/Excel: Calculating expected total return of bond at maturity

I am using an excel spreadsheet with the Bloomberg add-in to look a series of bonds. I am wondering if there is a clean way to calculate the expected total return (nominal future value, no discounting) of a bond assuming full payout at maturity. For example, in the case of a zero coupon bond, the total return would be: Par Value minus Dirty Price Paid. But for a non-callable bullet bond, I do not know of a simple "one cell, one formula" way to account for the coupon payments.

I tried to find a Bloomberg field that produces this but their total return field only calculates backward looking, not expected total return.

Upvotes: 0

Views: 1500

Answers (1)

DS_London
DS_London

Reputation: 4271

It sounds like the OP is after the expected cashflows of a bond, rather like the CSHF function returns on the Bloomberg Terminal. This table is available via the DAPI as a "Bulk Data" field DES_CASH_FLOW via the BDS() Excel function.

eg for a given ISIN code:

enter image description here

This brings back the flows for a 1mm notional position, based on today's settlement date. If you want a different start date you use the SETTLE_DT override.

To sum up the coupon & principal flows in one cell (assuming you are on an up-to-date Excel version), you can use this formula (assuming the ISIN is in cell B4)

=SUM(CHOOSECOLS(BDS(B4 & " Corp","DES_CASH_FLOW","ARRAY=TRUE"),2,3))

enter image description here

Bloomberg uses the "old-school" (CSE) array formula for BDS by default. By adding the ARRAY=TRUE option, you can instead get the more useful dynamic array version. What the formula is doing is simply adding up columns 2 & 3 of the cash flow table.

For a fully-featured function, if your Excel has the LAMBDA function then you can take it a stage further and create you own re-usable function. Put this formula into a Name, using the Name Manager:

=LAMBDA(isin,settle,SUM(CHOOSECOLS(BDS(isin&" Corp","DES_CASH_FLOW","ARRAY=TRUE","SETTLE_DT",settle),2,3)))

enter image description here

which you can then use wherever you like: enter image description here

NB. The settle date you chose has to be on or after the first settlement date of the bond (FIRST_SETTLE_DT).

Upvotes: 1

Related Questions