So we've taken a look here at the mechanics of the pension accounting calculation. But in this case, we're actually going to run through the numbers using Excel. To start with, you can see that we've made some assumptions here. We've got a retirement age of 65, mortality of 80. We've got a discount rate of 4%. We've assumed initially that the return on pension assets of 4%, we've got a final salary estimate of 100 and finally a final salary benefit of 60%. So you retire and you get a salary of 100, you'll get a pension of 60. So the pension payment each year at retirement, which you're going to calculate by taking the 100 times the 60% easy. Then we've gotta calculate the present value of the annuity that we are giving. So we're giving somebody 60 and that's going to be the rest of their life. So age 65 to 80, a 15 years period, a 15 year period. So we'll do equals minus the present value because it will give us a negative number and we'll start with the rate. And this is going to be the discount rate for the annuity, comma, the number of periods which is going to be the age that we expect someone to die minus the retirement age of 65. And then we've got the payment and that's the pension payment, that 60. And for simplicity, we'll assume that that's not index linked, it doesn't grow with inflation, it probably would do, but it just makes the math more difficult. So we'll just kinda ignore that. And then I'm going to close parenthesis and hit enter and that will give us the pension part that we need to build up. So that's 667.1 and you can see the formula there. Now we've assumed that the person that we're doing the pension for is age 60 and that's kinda unrealistic, but hey, it makes the numbers way easier because you've only got five years to do the numbers. So the accountants will then talk to the actuaries and say, okay, what does the pension pot need to be? And the actuaries have said, well hey, it needs to be 667.1. So we're going to take the 661.7. And I'm going to absolutely reference that. And then I'm going to divide it by the number of years that the person has got to work and that's, they retire at 65 and I'm track their current age and that's unrealistic, we know, but it's very simple. We'll take 60 and that means it's going to be spread over five years and I'll just copy that down. So this means that the amortization of that 667.1 is going to be straight line over the five year period. Now we can't use that straight line number in the accounts. What we have to do is we've got to present value it and we present value it to the amount of time that's left between making a contribution or estimate. Because this is effectively the service cost and the retirement date. And we'll assume that we're at the end of the year. So there's actually only four years left between the service cost calculation and retirement. So what I'm going to do is I'm going to take the 133.4 and divide that by open parenthesis, 1 plus the discount rate the 4% and I'll absolutely reference that again and I'll take that to the power of the number of years remaining, which is 4. And I'll get 114. And then if I copy that down, that will give me the present value in each year. And in fact, this last one here should be zero. So this means this, these numbers here that I'm selecting now, this is the service cost and that will run through the income statement. So let's take a look at mechanics of how this works in terms of the projected benefit obligation. And I'm actually going to fix my screens here. So we've got a little pension fund liability and we began with zero. And then I'm going to add the first year of service cost. So this is the projected benefit obligation that we're doing here. So that's 114. And then the interest. And to make things simple, what we're going to do is we're just going to calculate interest for the beginning balance. We'll assume that that service cost is kind of end of year one. So I'll take the beginning balance and I'll multiply it by the discount rate. And that's kind of showing the unwinding of the present value. Now because this has just started, there's no existing amount. So what I'm going to do is I'm just going to multiply the beginning balance of zero by 4% and then we've got the ending amount, which is just the sum of the beginning balance. I do equals first equals the sum of the beginning balance plus service cost plus the interest. And with making no actuarial adjustments here because that's going to make things much more complicated. So we're just staying simple then. And year two, we'll start with 114 that we built up from year one. We'll add the service cost. Note that the service cost has grown a bit because you are kind of one year closer and now you'll start to see the interest kick in. So we've got some interest and so the ending amount will build up. And then if I just copy this down, you probably know what's coming in year five. Yes, we get to 667.1 year pd. So that's how the accounting works for the projected benefit obligation. The pension fund liability. Pretty straightforward is just some financial maths and that's how we can see that the company calculates the service cost. Now it's probably at this point worth taking a look at the pension assets. And what I'm going to do here is I'm going to take off my freeze pays. Now for the pension assets, we're going to make the assumption first that the return on plan assets is equal to the discount rates. So there's kind of 20 20 vision here and that just makes the math easy. So this means that the contribution, if they've got 20 20 vision, cash contribution to the pension and asset fund should be equal to the service cost, right? Because that's the true economic liability that the company's taking on each year. So you should contribute at that to the pension plan. Now the return is again going to the beginning balance. We've got no assets built up in this scheme multiplied by the return on plan assets. And I'll just absolutely reference that. And again, the return plan assets at 4% is exactly equal to the discount rate. So this means that the ending amount is going to be the beginning plus contribution plus the return. And you can just calculate the return here by taking the return divided by the beginning balance. And we'll get that 4%, which is zero in year one.

So in the next year we'll start with the ending amount. The contribution will equal the service cost, the return. Now we'll start kicking in because we've got some assets and then of course that will build up. And you can see here this is pretty straightforward because what's happening is it's exactly the same as the liability. So here what we're seeing is the asset exactly matches the liability. And that's because there's no change to the assumptions and the pension assets are being invested at exactly the same return as a discount rate. Now you think, well why are pensions such an issue? Well, let's take a look at a situation where the return on assets don't equal the discount rate. So I'm actually going to do the pension liability first because pension liability doesn't really change, okay? Unless of course you change the assumptions. So what I'm going to do here is I'm going to just take the, it's the same service cost that we had before, okay? And then I'm going to take the same interest cost and the ending amount. In fact, I'm just going to copy this because it's not going to change. It's going to be exactly the same as we had before. So the pension fund liability doesn't change, but in this case, the pension plan is going to be invested in assets which are not going to give kind of a standard 4% return. They're going to be a mixed return, particularly if it's an equity portfolio. But we're going to assume that the contribution is still going to be equal to the service cost because that's the economic amount that we need. But the return is going to be different. Now, in the first year, I'm going to get a kind of divide by zero. So I'm actually going to just going to put zero there because otherwise I'll get there little divide by zero error, which is kind of annoying me. So I'm going to put zero there. So the ending amount, and I can just do a sum function here. If I just do a sum function, sum that all up. And then the beginning amount at the end of, or the beginning of year two Is 114, we again make the service contribution. But here now we've had a bit of a problem because the stock market's tanked, we've got a negative 10%. So I'm going to take the negative to 10% and multiply it by the beginning contribution. So this means that the ending pension plan is 221.3. Now you could say, well why doesn't the company then just contribute more into the scheme? Well, you could argue that, okay, we've had a bad year in the stock market this year, but it could come back, you know, because the average return should be pretty good.

But this means that at this point what we'll see that the, if I just take the value of the plan assets, which is 221.3 and subtract the value of the plan liabilities, in year one it was fully funded. But in year two, now it has come underfunded. And that's because the value of these financial assets have gone down because the stock market has fallen. Now next year it's come back, the stock market now has returned 10%. Fantastic. So the ending amount has jumped to 366.7. So you can see the asset is really quite volatile given returns. And we take a look at where we are. Well, we're still in a slight deficit, but it's come way back. So the company's pretty thankful that it didn't make any additional contributions because hey, it's volatile. And then in year three, what's going to happen? Let's assume that we make the standard service cost contribution, but the return here is 7%. Pretty good, higher than our 4%. So the ending amounts going to be 520. And let's take a look at where we are. Oh, we're in surplus because the asset is higher than the liability. Fantastic. And then let's do the last year. So we've got 520. The contributions, the service cost, the return in this case is, ooh is 1%. So it's worse than that 4% discount rate. Where are we at the end of the whole scheme's life? Let's take a look. 7.8. So in this case, the company probably would make an additional contribution to meet the liability. because remember, they've given the guarantee. So you can start to see that, assuming of course that the pension liability stays constant and we invested in the stock market because of the volatility, those returns we're going to get differences. Now remember, the accounting doesn't use the actual return. What it does, it will use the expected long-term return. So this volatility won't be seen on the income statement, but it will be seen on the balance sheet. Now finally, let's assume that the contributions are fixed here and we've still got this volatility. So the return, I'm just going to make zero. So what I'm going to do, I'm still going to copy these calculations down here and I'm going to copy these calculations here at the beginning balances. So here, what I've assumed is that the contributions, the cash contributions are going to scale constant. And the reason I've done that in this prior example, the contributions equal the service cost. The reason I've done this, I just want you to show you what happens if the assumptions change. So I'm going to make the service cost and everything else equal to the items above, and I'll just do the beginning balance. So initially what we've got here is a situation where the picture, the surplus and deficit, it's going to be exactly equal to the example above. Okay? So we've got the example above. So what we're seeing here is we have got swings between surplus and we've got swings between deficit because we've got a volatile asset base. But, and the reason I've got this third example is, remember what we said is that imagine a situation where you think, oh, actually, you know, we've made an assumption about people dying at age 80. Well, hey, there's this new drug that's come out that makes people last until 90. What will happen? Well, remember all those estimates that we made, all those kind of service cost estimates were made when we thought people were going to die at 80. If they die at 90. Now you can see that the scheme will get a massive underfunded amount because we haven't been making enough contributions because we've, our liability has been underestimated. And there can be other reasons for that. Let's just go and change. Another reason, more likely reason is the discount rate. Imagine if rates fall, and this is what's happened to a lot of companies. Let's say the discount rates falls to 3% because interest rates have come down because of quantitative easing. Let's see what happens then. If we've made the stable same contributions, you can see in this case, again, the scheme becomes seriously underfunded. So actually whether the scheme is fully funded or underfunded can actually be related to two things. Either the performance of the underlying portfolio or the assumptions going into the pension fund liability.