home

Why I Made a Bond Portfolio Analytics App

Mar 23, 2019

A couple months ago, I was looking for an idea for a Kotlin project, and I didn’t want to make another generic CRUD-style app. I wanted to build something that deals with a problem I’ve had outside of programming. So I started thinking about the issues I’ve had working with certain programs at my old job, specifically Excel and PowerPoint.

Excel and PowerPoint are powerful, robust tools. They’re the bread and butter in a wide range of industries. In Excel, you can store and manipulate data, create financial models, design nice charts and graphs, and chuck it all into a clean PowerPoint deck.

But software like Excel and PowerPoint have glaring flaws. Major problems crop up from overusing them.

I built an app that provides bond portfolio analytics without the hidden costs of spreadsheet and presentation programs. But why invest in another app when you can do the job with existing tools that most people are familiar with?

I mention some bond concepts in this post. If you’re not familiar with bonds, I wrote a short overview here.

Not Using the Right Tools

I used to work at an asset management company managing fixed-income portfolios where we spent most our time in Excel and PowerPoint. These tools worked well for us in a lot of projects, but we eventually hit their weaknesses the hard way. They’re hard to scale and maintain — and error-prone. We were using them as the go-to tools for all kinds of problems. This is referred to as Man With a Hammer syndrome.

We had issues with multiple versions of the same file. Data was stored everywhere — hard-coded in Excel files, internal presentation decks, in old emails and PDFs. All nestled in various directories we had to dig through.

Problems grew as we had virtually no documentation. If you were taking over a project leftover by someone who was no longer at the company, you prayed they left some notes in a word doc. Or else you’re stuck at the office trying to figure out where all the data came from and how it all fits together.

Trying to scale and serve hundreds of clients this way generated time-consuming work. We weren’t using the right tools for the job. We asked managers and other departments about introducing new software, but streamlining wasn’t a top priority.

When Do We Need Bond Analytics?

Let’s take a look at the City of Covina. Covina collects taxes from its citizens and spends the money on projects like parks, schools, and road repairs. Since Covina knows how to budget, they have about $77 million saved up based on their financials posted here.

The city doesn’t want their cash sitting around in a savings account or money market fund eroding to inflation. So they hire a portfolio manager to invest their surplus cash in a diverse portfolio of bonds.

The portfolio manager actively manages their portfolio, provides investment recommendations, and handles other overhead items like reporting, accounting, and compliance. In return, the portfolio manager gets about 12 basis points (0.12%) of the portfolio’s total assets every year. Not bad.

At the quarterly city council meetings, the portfolio manager would need to go over the city’s investments. The portfolio overview might include:

  • portfolio statistics including totals, weighted average yields, maturities, and duration
  • bond distributions by security type, maturity, and credit ratings
  • detailed bond listings
  • investment return and interest earnings
  • compliance checks

To get those results, the portfolio manager would need to

  1. import the bond data into Excel
  2. calculate portfolio statistics and distributions using Excel formulas and pivot tables
  3. create charts and tables
  4. transfer everything over to a PowerPoint presentation
  5. format the presentation and check for consistency/errors

Doing this in Excel and PowerPoint for a large number of clients is inefficient. Prebuilt models and templates help, but dealing with versioning, weak links, along with changing requirements is a nightmare.

An app could automate most of the work and reduce the potential for errors.

Accrual

I made an app called Accrual — it reads bond data and prints portfolio statistics, distributions, and detailed bond data. Accrual is written in Kotlin, and you can check out the code on github.

Example Output

Portfolio Statistics

┌─────────────────────────────────┐
│      Portfolio Statistics       │
│          As of 12/31/18         │
├────────────────┬────────────────┤
│ Par            │ 172,175,288.50 │
├────────────────┼────────────────┤
│ Market Value   │ 172,463,172.88 │
├────────────────┼────────────────┤
│ Amortized Cost │ 173,172,162.34 │
├────────────────┼────────────────┤
│ Original Cost  │ 175,780,852.49 │
├────────────────┼────────────────┤
│ Accrued        │ 750,033.51     │
│ Interest       │                │
├────────────────┼────────────────┤
│ Yield At Cost  │ 1.55%          │
├────────────────┼────────────────┤
│ Average        │ 2.81 Years     │
│ Maturity       │                │
└────────────────┴────────────────┘

Credit Distribution

┌─────────────────────────────────┐
│       Credit Distribution       │
│    Ratings by Standard & Poors  │
├────────────────┬────────────────┤
│ AAA            │ 6.9%           │
├────────────────┼────────────────┤
│ AA+            │ 59.3%          │
├────────────────┼────────────────┤
│ AA             │ 0.2%           │
├────────────────┼────────────────┤
│ AA-            │ 5.7%           │
├────────────────┼────────────────┤
│ A+             │ 13.3%          │
├────────────────┼────────────────┤
│ A              │ 5.8%           │
├────────────────┼────────────────┤
│ A-             │ 3.9%           │
├────────────────┼────────────────┤
│ BBB+           │ 1.5%           │
├────────────────┼────────────────┤
│ A-1+           │ 2.0%           │
├────────────────┼────────────────┤
│ NR             │ 1.5%           │
└────────────────┴────────────────┘

Since Accrual is a proof of concept, it uses a command-line interface that reads bond data from a file and provides an interactive shell for viewing results. Ideally, you’d want a feature-rich web app. Some nice features would include:

  • data visualization
  • compliance checking
  • viewing returns versus a benchmark over time

Companies can choose to use an external app or build their own. External apps tend to be more reliable since they’re tested by other customers, but less customizable compared to internally developed apps. But internal apps require a big investment since building software takes time and good engineers are expensive/scarce.

All in all, don’t pick the same set of familiar tools to solve every problem that comes along. Explore alternatives. Or you might end up hammering in screws.