Help with a numbers spreadsheet

[trooperj3]

Not sure if this is the correct Forum to ask for help with this but here goes.

I have created a spreadsheet to share out an inheritance portfolio between my two children. One of my children has also created a spreadsheet but we don't arrive at the same figures for the split of the portfolio when inputting the same scenarios.

We have helped one of our children, Brenda to purchase a flat. If we give Brenda no further funds from the portfolio until both children inherit then both Robert, my sons spreadsheet and mine arrive at the same share of the portfolio. This was simply done by applying the same growth in the portfolio from the value it was when Brenda had her help to the final portfolio value. That same growth was applied to the money we gave Brenda and that theoretical amount was taken from the final portfolio value and the balance of the portfolio was then equally shared.

The discrepancies appear in the event that we give Brenda further money from the portfolio. Our different spreadsheets allow for up to two separate further payments to be made to Brenda but thats when the potentially large discrepancies show in the two spreadsheets for the final inheritance for Brenda & Robert.

Can anyone show me the correct way to create an accurate spreadsheet that would show the two final values that our children would inherit in the event that up to two additional payments are made to Brenda from the portfolio at some future date.
The portfolio value was £150,000 before we gave Brenda £40,000.

Thanks for any help

 

[KaliYoni]

Before I comment, I want to say this should not be used as legal, estate planning, or financial advice. My intent is to help you troubleshoot a situation where two analyses seem to calculate a different answer using the same data.

1. It might be easiest to compare the formulas used in each spreadsheet. Are they exactly the same?
2. Next, check the assumptions and starting values used in each spreadsheet. Do they match exactly?
3. Another frequent source of spreadsheet problems is using an auto-extend function to replicate a calculation over multiple cells. So make sure the correct cell references are used in any repetitive calculations.

Now, an idea to simplify the overall analysis:

• Assume the inheritance shares begin and remain equal. Treat the payment to one party as a loan, with a standalone calculation of beginning and ending value. You can choose any interest rate you feel is appropriate, such as zero or the growth rate you're assuming for your portfolio. Deduct the ending value of the loan from the ending value of the inheritance share.

 

[trooperj3]

Before I comment, I want to say this should not be used as legal, estate planning, or financial advice. My intent is to help you troubleshoot a situation where two analyses seem to calculate a different answer using the same data.

1. It might be easiest to compare the formulas used in each spreadsheet. Are they exactly the same?
2. Next, check the assumptions and starting values used in each spreadsheet. Do they match exactly?
3. Another frequent source of spreadsheet problems is using an auto-extend function to replicate a calculation over multiple cells. So make sure the correct cell references are used in any repetitive calculations.

Now, an idea to simplify the overall analysis:

• Assume the inheritance shares begin and remain equal. Treat the payment to one party as a loan, with a standalone calculation of beginning and ending value. You can choose any interest rate you feel is appropriate, such as zero or the growth rate you're assuming for your portfolio. Deduct the ending value of the loan from the ending value of the inheritance share.

Hi,
Thanks for your reply and the trouble you have gone to.
We have not treated payments as a loan with an appropriate rate of interest, rather have based the growth on the actual % growth of the portfolio at the time any further payments are made and the % growth to the final value of the portfolio.
On my spreadsheet I have allowed for the actual figures to be entered on the day that any further payments are made to Brenda, ie the payment and the portfolio value. Then I have applied to the same % growth between 1st and 2nd payment and between the 2nd payment and the final portfolio value to the theoretical figures that they would have been without further payments. I did this to allow for the compounded growth between payments.... I think my logic is correct though it may be flawed and/or the way I have calculated it may be wrong.
Although my son, who lives abroad, has sent me a link to his spreadsheet it is not something that I can easily follow, hence my wishing to know if my logic and method is correct such that any actual figures entered would reflect accurate future figures.
I am happy to send you my spreadsheet for you to look over, that is if you feel that you could/wish to help me further.
But in any event thanks again for you help.
Regards
tJ3

 

[KaliYoni]

Sorry to not be able to provide you with an easy solution to this, but if you aren't already comfortable with doing discounted cash flow and time value of money calculations, it's probably best if you speak to a professional financial planner and perhaps an estate planning specialist. Both this sort of analysis and the decisions that go along with it carry a lot of financial, legal, and, often most importantly, emotional weight. It's worth spending some money up front to ensure you're making the best possible decision for you and your family. Further, having a neutral third party involved can help eliminate or minimize conflicts and arguments with loved ones.

Also, be very careful about sharing both computer files and personal financial information with strangers!

 

[trooperj3]

Good morning,

Thanks again for your reply. Discounted cash flow/time calculations are not my field but I am a reasonably astute person.

I appreciate your advice of which I am already very much already aware. The names and figures in my spreadsheet are fictional as I would never divulge any personal information to strangers and certainly not on the internet!

However I am comfortable asking for help with my spreadsheet as the spreadsheet I have created contains no personal information, it is just my logic applied to a series of calculations. I am not saying that I would use whatever method someone else would use to calculate the possible scenarios I have described, but I would be very interested to see what method and what calculations they would use and how they would compare to our two spreadsheets when entering different values for amounts taken out and growth of the portfolio at those dates and of course the final value of the portfolio at the time of inheritance.

If the spreadsheet logic and calculations are mathematically correct then whatever figures were entered into the cells relating to the value of the portfolio when the initial payment was taken out, the amount taken out and then the same for up to two further payments (if any further payments at all) and the final inheritance value of the portfolio then the final cells displaying the amounts for the split should also be correct.
However mine and my sons spreadsheets only arrive at the same conclusion if no further payments are made after the initial payment.

I hope that makes sense and sets your concern at rest.

Thanks again for your input.

 

[BrianBaughn]

If today you give $40K of $150K to Brenda then Robert should have what would be considered a separate portfolio worth $40K (which Brenda doesn't share) and the remaining $70K portfolio would be split between them.

In the future, if you add $10K cash, for example, to your portfolio that goes to the shared portfolio which would be now $80K. Robert still has his own $40K portfolio.

If the total portfolio value increases or decreases by a percentage due to interest or whatever then that percentage would be applied to both the Robert portfolio and the shared portfolio. If it's +5%, for example, the shared portfolio would be $84K and the Robert-only portfolio would be $42K.

At this point, if you give Brenda another $10K then you'd add another $10K to the Robert portfolio, making it $52K. The shared portfolio would then be $64K (84–10–10).

And so on.

This prevents Brenda from earning interest (or suffering losses) on money she has already received.

 

[trooperj3]

If today you give $40K of $150K to Brenda then Robert should have what would be considered a separate portfolio worth $40K (which Brenda doesn't share) and the remaining $70K portfolio would be split between them.

In the future, if you add $10K cash, for example, to your portfolio that goes to the shared portfolio which would be now $80K. Robert still has his own $40K portfolio.

If the total portfolio value increases or decreases by a percentage due to interest or whatever then that percentage would be applied to both the Robert portfolio and the shared portfolio. If it's +5%, for example, the shared portfolio would be $84K and the Robert-only portfolio would be $42K.

At this point, if you give Brenda another $10K then you'd add another $10K to the Robert portfolio, making it $52K. The shared portfolio would then be $64K (84–10–10).

And so on.

This prevents Brenda from earning interest (or suffering losses) on money she has already received.

Thanks for your interesting method and input which I appreciate.

However we gave Brenda her first payment nearly two years ago and the portfolio is the combined total of mine and my wife's individual portfolios. We don't wish to start a new portfolio for Robert from our combined total as doing so will incur set-up charges as well as restricting our access to the portfolio during our retirement. Both my wife and I have recently retired.
This combined total will eventually be equally shared between Brenda & Robert when they eventually inherit but taking into account any payments that we have made to Brenda out of the portfolio before then.

My logic was to base calculations on the combined portfolios growth at each stage of the payments and apply that same % growth to the portfolios value as it would have been without any payments. Then do the same for each further payments.

I've tried attaching my spreadsheet but my spreadsheets are greyed out. Apparently numbers does not have the allowed extension to attach to a reply. This seems rather strange to me on a Forum where members are asking for help on numbers spreadsheets.

It would be good to know if my methodology and calculations are correct or if my methodology/calculations are flawed and if so why?

Thanks again

 

[BrianBaughn]

Thanks for your interesting method and input which I appreciate.

However we gave Brenda her first payment nearly two years ago and the portfolio is the combined total of mine and my wife's individual portfolios. We don't wish to start a new portfolio for Robert from our combined total as doing so will incur set-up charges as well as restricting our access to the portfolio during our retirement. Both my wife and I have recently retired.

I wasn't really suggesting that you actually open a separate portfolio…just that you consider the portfolio as divided.

This combined total will eventually be equally shared between Brenda & Robert when they eventually inherit but taking into account any payments that we have made to Brenda out of the portfolio before then.

This is exactly what my post said except that I am suggesting that Brenda not share in earnings for what she has already received.

My logic was to base calculations on the combined portfolios growth at each stage of the payments and apply that same % growth to the portfolios value as it would have been without any payments. Then do the same for each further payments.

That sounds like you want to pay interest to Brenda on money that you have loaned to Brenda. I've never had a loan like that!

 

[trooperj3]

That sounds like you want to pay interest to Brenda on money that you have loaned to Brenda. I've never had a loan like that!

No, that is not our intention. I am purely using the growth of the portfolio from the value it was when we gave Brenda the initial £40k to the day they both inherit (say for arguments sake it has doubled). I would then apply that same growth to Roberts share at the time of their inheritance, ie give him £80k and then split the remaining amount of the portfolio 50% each to Robert and Brenda.
That is a simple calculation to include in the spreadsheet and both my spreadsheet and my sons spreadsheet arrive at the same figures in that scenario.
The discrepancies appear between our two spreadsheets when one or two further payments are made to Brenda between now and when Robert and Brenda finally inherit.

 

[BrianBaughn]

Since I can't see your spreadsheet there's no way to compare between our descriptions.

 

[trooperj3]

Since I can't see your spreadsheet there's no way to compare between our descriptions.

See if this works. I've attached my spreadsheet but changed the file extension to pdf. Just change it back to numbers.

 

[BrianBaughn]

Sorry…that looks too convoluted for me to parse! I think I've already stated what the spreadsheet should entail.

As has been suggested, you should probably talk to a planner.

 

[trooperj3]

Sorry…that looks too convoluted for me to parse! I think I've already stated what the spreadsheet should entail.

As has been suggested, you should probably talk to a planner.

Ok, I appreciate your input and thanks for all your replies.