Core Capital Estimation with Mortality Tables

Introduction

Core capital is defined as ” the amount of capital required to fund spending to maintain a given lifestyle, fund goals, and provide adequate reserves for unexpected commitments”. In another way, it is the money “needed” for the rest of the one’s life.

The concept of core capital is pretty important. In estate planning, one will need to clearly understand how much capital you have to pass on to others (family, charity, etc). Just like financial reporting for a company, it is not too difficult to come up with something similar for a family, list the total asset and liabilities. If we factor in the total liability including the future spending (core capital) and also the human capital of all future projected employment income. We will be able to find the “excess capital”. In that case, to accurately quantify the core capital is an important element in estate planning.

In order to calculate that, we will need know people’s life expectancy, spending for each year and take the time value into account to calculate the total present value.

Mortality Table

It is surprisingly predictable at an aggregated level regarding people’s lifetime expectancy. Just like for a group of kids, it is very likely all of them will survive for another 10 years and as certain as almost none will survive another 50 years post 65.

There is a table that listed many of the statistics related to survival of different age called mortality table. By a quick Google search, one can easily find several published mortality table listed by US CDC (United State Center for Disease Control).

The document that I found was categorized into different tables based on demographics, Hispanic, White and Black and the corresponding male and female.

Head of the mortality table
Tail of the Mortality Table

Most of the statistics are fairly easy to understand and you can refer to this report – National Vital Statistics Report for the detailed definition and explanation of each field. (quite a fun read!)

Of course, if we need to calculate the core capital required for a family, then one will need to use the joint probability calculated as subtracting one by the probability that both couple will die.

Discount

Given the probability of surviving each year, we will need to estimate the spending needs for each year and adjust it accordingly. For example, there is the risk free rate which we can use, however, we also want to take inflation into consideration so our discount rate is likely going to be the nominal risk free rate minus the inflation rate.

Double Taxation

Background

Countries impose taxes to maintain its operation. Different countries have different tax systems. When cross-border business happens, it become more interesting. In order to for an income to occur, it usually includes two “where”s – one as where the income was originally sourced, aka. where the money was made, and another one as where this successful business owner resides.

There are countries which taxes all incomes incurred within its boarder is claimed to be source/territorial tax system. On the other hand, countries could be tax based on business owners’ residency, for example, it might not tax aliens but at the same time, it will impose taxes on its own citizen’s foreign income – residence tax system.

If you are unlucky, you might have to pay tax twice, maybe because your business happens to qualified as sourced from both countries, maybe because of your duo citizenship, or as your income got taxed when it was sourced in one country, and also a different country where you reside impose another income tax again afterwards – that is called double taxation.

Usually countries work together to mitigate this effect, where the money got sourced usually take the first cut, and then the residence country will provide some tax code to accommodate. In this case, the source country will tax its own fixed amount say 40%. How much extra its own resident should pay will be calculated in three ways: credit, exemption or deduction.

Foreign Tax Credit Provisions

Exempt is the most straightforward as if its resident paid income tax to its source country, then there won’t be any further taxation – simple and straightforward.

Under credit method, one will pay the residence country the amount which residence tax is higher than its source. If the source is 50% and residence is 20% only. No need to pay more tax after you got taxed 50%. However, if the source is 20% while the residence is 50%. One still needs to pay the delta of 30% to its own residence country. One way or the other, the residence country need to maintain the total tax rate as being max(T_source, T_residence).

Under the deduction method, it is just like various types of individual income deduction from medical, car tax. The residence country will only tax on the portion post tax. so it will be T_residence * (1 – T_source). The total tax rate will be T_source + T_residence * (1 – T_source) = T_source + T_residence – T_residence*T_source.

So let’s take a look at an example, if we hold T_source to be a flat 40%.

Under the credit method, the total tax rate will be max(T_residence, 40%) and under the deduction method, the total tax rate will be 40% + T_residence 60%.

Example – Credit better than Deduction

one interesting observation is that the credit method is always more effective for the same amount of source tax rate.

And if we plot the ratio of credit vs the deduction method.

We can see that the credit method is always lower for the tax payer and the marginal benefit is the highest when the residence tax equals to source tax. That being that, when the residence tax is extremely high or extremely low, both methods converges to T_residence which is not a surprise after all.

Tax Loss Harvesting

(all content here are for academic and self-entertaining purpose, check the facts with authority before you can believe it is true and the authors claims no responsibility for misusage or misinterpretation)

One can make money or lose money by investing in stocks. Stock price usually goes up and down, people usually lose money by selling their stocks out of panic during a market downturn which also means missing out following up-turn (aka. mean reversion). There is a saying “it is not a loss until you sell” which tend to urge people don’t sell in the scenarios like this.

However, there is a technique in investments called tax loss harvesting. This technique is meant to take tax into consideration and demonstrated under certain/most scenarios, it is a better decision to realize the loss, and used the tax saved to reinvest to achieve superior performance than “being passive”. This post is meant to go through an example, explain how tax loss harvesting works and highlights some of the underlying assumptions.

If I have a portfolio which is a mixture of company stocks with the total value of $100K at the beginning of 2020. By the end of 2020, we have recognized a $10K gain by trading. Of course, some stocks went up and some went down, assuming that some of the stocks literally dropped the value $10K or even more. And the tax rate is at 20%.

The question is what difference will it make if I sell those “losers” and replace with more promising ones.

The baseline scenario is that if we don’t sell those losers. We will need to pay 20% on those realized gains which is $10K * 20% = $2K taxes.

If we sell those losers with a realized loss of $10K. That completely cancel out the gain so far and the net gain will be 0. No tax for you this year or the tax saving from the baseline is $2K.

Free money?! No.

As one can tell, just by realizing the loss, you immediately eliminate your tax this year but taxes are NOT gone. You are postponing by reducing the cost base for your newly invested replacements for those losers. For example, if those $10K losses were from a $50K initial investment, after you realized the losses and reinvested the $40K left buying new stocks. If in the future, the price goes up and you try to harvest the gain then. You will need to use the $40K as the cost basis rather than $50K if you did not sell it. So basically you still have to pay the tax for the “losses” then. Hence, tax loss harvesting in nature is a type of tax deferral.

Let’s go through an example, if those securities or replacement securities have its value rise to $100K, and we will sell it one way or another by end of next year. In the baseline scenario where we don’t realize the loss at end of year 1, at that time, our gain will be ($100K – $50K) = $50K. And we will need to pay 20% of those gains as taxes which equates to $10K. And if we realized the loss, our gain will be instead be ($100K – $40K) = $60K. And our taxes next year will be $12K.

TaxBaselineRealize Loss
Year1$2KN/A
Year2$10K$12K
Total$12K$12K

As you can tell, no difference. Let’s parameterize it.

TaxBaselineRealize Loss
Year1T1T1 – L * T
Year2G * T(L + G) * T
TotalT1 + G*TT1 – LT + (L+G)*T = T1+G*T

As you can see, the realized loss item will be cancelled out in the future by the time you sell it anyway so there are only two scenarios that this “extra work” worth doing, a different and lower tax rate in the future, or reinvest from deferred tax.

If you understand the time value of money, a dollar today is not the same as a dollar tomorrow, or a dollar next year. By defering any type of cash outflow in the future will reduce the net present value of the cash outflow. Minimally speaking, it will be the inflation rate but in the investment cases. If there are better ways of leveraging those cashes to reinvest (buying new promising stocks), the difference can be substantial.

So basically, in this very example, you have $2K at your leisure to invest. Any return on those $2K will be net gains. Or in more general, L * T amount of money that you can use. Almost, like the higher amount, the better it is if and only if you can realize extra gains from those.

In the grand scheme, this whole tax loss harvesting might not be substantial. Why? The tax alpha (abnormal return) is basically how much money you can gain out realized tax loss, tax rate and the return out of that. We say L * T * r.

For example, if the loss rate is r_l and the positive is r_g. The total is r_l * T * r_g. This thing can only be substantial under the premises that your loss was somehow huge and then all of a sudden you have a promising investment. In regular days, this rarely happens but at big market downturns, this return can be substantial. r_l being -50% and r_g be +50%. That is 25% with a tax rate 40%. You got 10% return rate just out of tax harvesting loss.

Also, if you do this on a consistent basic, little things add up and you also gain a compounding effect of wealth accumulation by deferring tax payments on a consistent basis (how long it got deferred is now up to how soon you sell it, if you realized the gain this year, reinvest and sold it far far in the future, theoretically, you deferred it for a very long time).

In the end, to summarize, tax loss harvesting is one way to take advantage of the two-sided tax regimes in both most countries to passively allow gains to grow unharvested, but actively realizing losses. Again, this by no means to encourage purse losses, nor even give any level of comfort that loss is good, it is just another way to get some value out of a shitty situation. Just like not to intentionally make make your food go bad, but if it did, why not a make a banana bread out it.

How to learn PowerBI like a boss

That was a fun title. I came across the phrase “like a boss” after watching too many youtube videos and now tend to associate it with “doing something really well”. It is also a pun to me as personally I have been at a management role for years so I am at a disadvantage of mastering a tool because now practice and time now is a luxury. However, constant learning is part of game, having some level of familiarity with the tools can help a manager so much from talents evaluation, strategy planning or even your day to day. If the team is busy delivering, the least thing you want to happen is to distract them by saying “can you help me plot this”.

This article will cover some of the ways that I am learning PowerBI and I will also include some resources here and there.

Use it when you can

This is kind of a golden rule for learning anything, right? When you are learning a second language, the teachers always say speak as much as possible, and as often as possible. A tool like PowerBI is not an exception, it has a great overlap with tools like Excel, Database and many others. I guarantee you that there are plenty of opportunities you can use PowerBI every day. When those opportunities come up, the challenge is how do you balance your work efficiency and your learning speed. It is probably not a good idea to learn how to use IT when you present to your executives, but if you are alone at home trying to do some JIRA reporting, maybe give it a whirl.

There are several ways of helping it.

  • Pin PowerBI to your taskbar so you see every day
  • Temporarily delete/hide other competing tools, try not to use R, Python or Tableau if possible
  • Make a goal of spending 30 minutes everyday after work for PowerBI

RTFM

Like reading the source code of Pandas, for proprietary softwares like PowerBI, there is no better way to learn the features by reading through the materials published by Microsoft.

https://docs.microsoft.com/en-us/power-bi/

Community

It is important to have some comrades along this journey. Someone that you can communicate and share. If you have the privilege of working in large corporation that happen to have coworkers that also learning who already specializes in PowerBI. Then you are in luck. Spend more time with them, pair programming and IM them about your questions definitely is a good way. If not, PowerBI has a forum, just like stackoverflow is the wunderland for Software Developers, by asking and answering questions on the forum is certainly a good way to be part of the community.

Last but not the least, youtube is always your best friend.

A good channel to follow is the “Guy in a Cube” which Adam and Patrick talk about some of the functionalities of PowerBI in a very practical and concise way, absolutely subscribe and follow through their videos.

Course

Also, one thing I do have to say is controversial is to take courses. Systematic learning vs fragmented learning are definitely different ways of learning. One thing I found difficult about taking courses is you learn solutions without the problem. They teach you all the functionalities but without a lack of context or real life problem that you personally want to solve. However, I do have to say that systematically take a course and follow it through has the benefits of covering all the aspects at one stop. Even if you still need to look up certain functionalities when you use, you will have a systematic index in your brain that you can find the solution easier than going through every problem by a Google search.

Self Paced Learning from Microsoft

PowerBI from EDX

Conclusion

不积小流无以成江海,不积跬步无以至千里

Taking shortcut in learning is a pretty dangerous mindset to get in, so dangerous that it will hinder your growth once you learned the basic tricks. A good mentality to have is to recognize that you are a student and have an open mind and take actions to learn in a ruthless way.

Today, I just learned how to use slicers rather than Filter Pane to save real estate on your screen. What have you learned?

Small Chat Room using Kafka and Flask

I have always been interested in learning more about consumer applications like building a chat room, this weekend, I managed to build a chat room using Kafka and Python flask just in a few hours.

The technology stack is very simple. It is primarily using Pytho Flask, very limited HTML and JavaScript. I had to use Redit to get the flask SSE working and Kafka as the message queue system, that both of those two components requires close to minimal set up.

The architecture is very straightforward too. When the client entered a message and clicked submit, a JavaScript function will be trigger, pass on the username and message to a python flask endpoint. There must be a JavaScript library to directly connect to Kafka but I feel it is cleaner to keep JavaScript separated from the backend like database directly, rather to hit the middleware API endpoints. That will for sure free the clients from having visibility into any backend structure from the security perspective, also created some flexibility of any modification to the backend without impacting any front end work. Then the Flask Sendmsg endpoint will use the python kafka library to produce a message into Kafka.

As you can tell from a consumer who has been subscribing to the topic, all the messages are now in the queue. The idea is whenever there is a new message, there is a mechanism to detect and then “broadcast” or “publish” to all the “subscribers”, or all corresponding clients who happened to be in the same chat room.

At this moment, I don’t know how to create a long running process within Flask so I created a small Python consumer running in another script. It listens to the topic and push any new message to another Flask endpoint called “broadcast”.

The broadcast endpoints does nothing more than taking in the new message, but it will publish it via server side event. There are plenty of benefits of using server side event comparing with polling or sometimes even a open websocket.

Now, all the clients have an event listener to the SSE stream. The any received new message from the stream will trigger the newly received message be appended to the chat history.

More work can be done around the Kafka part as the user’s chat history could be retrieved from a database or we use Kafka as the database directly. And the offset will determine to how long a history that we will go back.

Anyway, it is a pretty fun project overall. 🙂

Excel – Workday

Today I learned a few tricks in Excel which are pretty fascinating.

WORKDAY

Today my coworker need to add some delay to an existing date (more like adding the number of days processing the order on top of the order receiving date). It could work as a simple addition by adding a number to any date cell. However, my coworker insisted on using a function called WORKDAY which I have never seen before. It turned out to be such a simple concept while providing a scenario more realistic – most people don’t work during weekends 🙂

Format Weekday

Another thing that I realized in Excel is the various ways of formatting dates. One way to extract day of the week is by simply using the format and use ddd (Wed) or dddd (Wednesday).

Observable

Today I came across Observable and really like it at first glance. It gave me the impression as being an online IDE like Jupyter Notebook but for JavaScript.

Javascript tend not to be the first programming language that schools teach. For most engineering related majors, they tend to start with strong typed programming language like C++, Java, C and now maybe more schools start by teaching Python too. However, JavaScript gained huge adoption in the community as it is the most popular scripting language for the Internet. However, the first question for all the beginners is “where should I write code”. I frowned upon when my friend opened up the Chrome browser and start writing code there. It was powerful and all but somehow it just did not feel right to me. There was no canvas like Jupyter notebook, you have to add the graph to an existing DOM etc.

In the observable notebook, everything become so much easier to test and visualize, also the code can be executed and the output is reactive in a way that you can execute a block of code and see the output immediately.

You can not only write JavaScript code straight into block, but also use Markdown language to format in a way that you will like.

I am still learning it but I highly recommend people check it out. You might have heard of D3.js, they even use observable to host all their gallery examples. This further demonstrated its popularity in the JavaScript visualization community.