Pension Financial Modelling with Microsoft Excel

I am looking for someone to create a Microsoft Excel spreadsheet that will model the cash inflows and outflows for my pension arrangements.

My pension arrangements have 3 separate pensions - 2 are very straightforward. Both have a start date, are of a fixed amount per month and are expected to increase every year by 3%.

The other pension is more complicated but still quite predictable . This pension receives Rental Income from properties in the pension fund. The fund has a bank account from which my pension (Distribution) is paid. The fund also has an Investment Account for Shares. If there is insufficient money in the Bank Account then shares are sold from the Investment Account and the money transferred to the Bank Account. When there is insufficient funds in the Investment Account then a property will be sold and the proceeds moved to the Investment Account.


It is comprised of the following elements:

1) Property Portfolio
There are 5 properties. For each property the model must make allowance for the following:
a) Current Rental Income which goes to Bank Account
b) Rental Income Amount will increase each year by an assumed percentage amount - Rental Income Growth Percentage
c) Property Value
d) Property Value Growth Percentage
e) Maintenance Cost Percentage - This to be a variable for each property)
f) Future Property Disposal Date - If a Future Disposal Date is entered, Rental Income will cease and the Property Value will be moved to the Share Portfolio Account

2) Bank Account
Bank Account will have the following elements for the purpose of the model
a) Opening Balance - For each year = to the closing balance for previous year
b) Income ( Rental income from properties)
c) Distributions ( Payments out from the Bank Account to me)
d) Pension Management Fees ( Payment out Pension Mngt Company ) Assume 5%
f)

3) Share Portfolio Account
a) Opening value for each year will be closing value for previous year + Investment Growth Factor - Assume 6%
b) Share Disposals in the model will be recorded as a date and amount. From that date the Sales Disposal amount will be deducted from the Share Portfolio Account and moved to the Bank Account

Basically I want to be able to use the model to simulate different Distribution Amounts per year and see how this impacts the overall declining value of the pension pot. Also I want to be able to simulate the impact of selling properties and to see how long my pension will last at different levels of withdrawal.

I am 63 years now and I want to be able to insert different distribution amounts per year from now until I am 99 years.





.