I was curious if the "Last Year" column comes into play. On row 182, the value in that column is 2018, there is no amount for 2019 and there is an amount for 2020 (286,646). Is the model blind to the gap because we stop it before the gap? 2018?
The way the model works, I'm taking cash out of a balance, so I care what the last year I have complete cash flows and asset balance for. I will roll forward from the last amounts - increase (or decrease) cash flows based on the last year I have, and increase/decrease the asset balance accordingly.
When I'm calculating growth rates, I'm just doing point-to-point comparisons, not trying to average annual amounts. So basically, it's blind to gaps in information.
In the past, when a gap has caused calculation problems, sometimes I just remove the specific pension plan from the choice list (and I email the database folks to see what's up.) Sometimes certain years are missing because there are audit issues for specific years, or something else -- they are still primarily dependent on a manual process to populate the database, so sometimes they have to clear out stuff. I let them know when I find data problems.
Yeah, and I'm looking at the Pittsburgh Municipal, and that's one I usually deleted from earlier versions of this tool (because of the data problems with that one). That one is just plain problematic.
It ignores any of the "real" data past the "LastYear" point, whatever I designated it to be.
In the case of Pittsburgh, I decided 2018 was the last year I could count on - with the gap in 2019, I just didn't want to start with 2020. Yes, there is a manual process with me choosing what the "last year" for reliable data is for the weird ones. I should have just deleted that one because of its problems. It's just one of the plans I don't look at that often (unlike the Chicago plans)
When the Public Plans Database does their next data update, I'll check to see if it looks any better -- if it doesn't, I'll just remove it.
I will check out your spreadsheet soon. I did some work on government pensions, Jamaica, in a distant past.
Interesting -- I am not as familiar w/ government pensions outside the U.S., though I have learned about some Canadian systems.
In general, I have found that the benefit design, specifically what aspects are guaranteed (and what isn't) pretty much drive everything else.
I was curious if the "Last Year" column comes into play. On row 182, the value in that column is 2018, there is no amount for 2019 and there is an amount for 2020 (286,646). Is the model blind to the gap because we stop it before the gap? 2018?
I have a simple question. On Sheet "Last Year Lookup" row 182. How do you treat gaps? Pittsburgh Municipal 196 PA 210,406 230,863 223,576 286,646 2018
The way the model works, I'm taking cash out of a balance, so I care what the last year I have complete cash flows and asset balance for. I will roll forward from the last amounts - increase (or decrease) cash flows based on the last year I have, and increase/decrease the asset balance accordingly.
When I'm calculating growth rates, I'm just doing point-to-point comparisons, not trying to average annual amounts. So basically, it's blind to gaps in information.
As I say, this is a very simple model.
In the past, when a gap has caused calculation problems, sometimes I just remove the specific pension plan from the choice list (and I email the database folks to see what's up.) Sometimes certain years are missing because there are audit issues for specific years, or something else -- they are still primarily dependent on a manual process to populate the database, so sometimes they have to clear out stuff. I let them know when I find data problems.
Noticed this after my previous post! (Smile)
Yeah, and I'm looking at the Pittsburgh Municipal, and that's one I usually deleted from earlier versions of this tool (because of the data problems with that one). That one is just plain problematic.
It ignores any of the "real" data past the "LastYear" point, whatever I designated it to be.
In the case of Pittsburgh, I decided 2018 was the last year I could count on - with the gap in 2019, I just didn't want to start with 2020. Yes, there is a manual process with me choosing what the "last year" for reliable data is for the weird ones. I should have just deleted that one because of its problems. It's just one of the plans I don't look at that often (unlike the Chicago plans)
When the Public Plans Database does their next data update, I'll check to see if it looks any better -- if it doesn't, I'll just remove it.
I plan to meddle with your tool some more this week. I like to play with structures and formulas. Thank you for sharing.
Oh yes! Let me know if you find anything interesting... ;)