I am seeking assistance with the completion of an excel finance spreadsheet. The spreadsheet is 95% complete and requires someone with some expertise in completing advanced financial formulaes in excel.
I require the spreadsheet to be completed by early next week, so we can use it model up various scenarios and calculate returns on investment for our investors over a 60 month period.
I can give specific details about exactly what is required in the spreadsheet to people who are interested in taking the project on. I have also attached a copy of the spreadsheet in question so it can be viewed to see what has been completed to date.
Budget Summary - Monthly Instalments
These are P & I payments made to us by the people buying homes off us. At present the calculation. So the P & I payment to be calculated for each property we have sold is as follows:
(Original Purchase Price x Property Mark-up (7.5%)) less Purchaser Deposit of 5%. In this case it wll be ($300,000 x 7.5%) = $322,500 less 5% deposit of $16,125 = $306,375 as the loan we are issuing the purchaser. The base bank interest rate plus our interest rate margin will be charged to the purchaser and this will be the new rate the P & I loan is calculated at for each property.
What I would also like is the ability to cater for a delinquency payment rate here. That is an ability to have a rate for 7 day late payments, 14 day late payments, 28 day payments and contract rescissions that adds to a % of Revenue not received each month so we can see what effect that has on the profitability of the investment when these variables are taken into account.
This is the payout the fund receives when the purchaser completes a contract with it. The calculation is as follows:
Loan amount less principal paid. So if the property completes in 3 years it will be the loan amount less the cumulative principal over 3 years.
In order to get the completion timeframes automated, I would like to have a function in the spreadsheet that automatically completes a purchase when the capital growth of the property + the purchasers deposit + principal paid by the purchaser equates to 10% of the property sale price (Base property purchase price + property mark-up margin). This will require a capital gain input within the spreadsheet that is able to be varied monthly.
Completion Deposits - 100%
Function is all good and doesn't need to be altered.
Completion Deposits - 30%
Not used and can be deleted
Capital Raising Fees
To be made a % of the capital raised in the Investment Register. So for example if the rate is 5% then the 5% would become payable the month the capital was raised.
Fund Management Fees
An imput in the Input Expenses sheet as a %. The calculation is to be paid monthly based on the amount of funding under management. That is total capital raised at the point of time each month. If there is $1,000,000 under management at month 1, then the payment would be 2%p.a/12*$1,000,000 for that month. If there was another $1,000,000 raised in month 2, then the payment would be 2%p.a/12*$2,000,000
Interest - Tranche 1
Interest Inputs to be varied so we can change the rate of interest on a monthly basis if there are any interest rate fluctuations made by the Berserve Bank. The interest charged will be on the total amount borrowed in Bank Funding - Phase 1, 1 month in arrears. So if the total borrowed at the end of month 1 for bank Funding - Phase 1 at row 71 is $1,000,000 then the interest paid at month 2 would be the monthly interest rate (6.80%/12)*$1,000,000. Heading to also be altered to Interest - Phase 1
Interest - Tranche 2
Interest Inputs to be varied so we can change the rate of interest on a monthly basis if there are any interest rate fluctuations made by the Berserve Bank. The interest charged will be on the total amount borrowed in Bank Funding - Phase 2, 1 month in arrears. So if the total borrowed at the end of month 1 for bank Funding - Phase 2 at row 77 is $1,000,000 then the interest paid at month 2 would be the monthly interest rate (6.80%/12)*$1,000,000. Heading to also be altered to Interest - Phase 2
Legal & Duty
Heading to be deleted
Calculation derived from the Inputs - Expenses (Housing Costs - Legal Fees) Cell Input*New Properties Each Month. The input increases by CPI each year and is currently working ok. No alteration is required.
Calculation derived from the Inputs - Expenses (Housing Costs - Stamp Duty) Cell Input (E23 Formulae)*New Properties Each Month. The input
Building and Pest
Calculation derived from the Inputs - Expenses (Housing Costs - Building & Pest) Cell Input*New Properties Each Month. The input increases by CPI each year and is currently working ok. No alteration is required.
These are the capital injections used to purchase the properties we are selling. The fund deposits are deducted from the available balance in rown 68. When the fund runs out of money, the bank funding commences at the leverage ratio input at cell D8 of the Input - Expenses sheet.
The objective here is to ensure all the available funding from row 68 is used before the loan is drawn down in row 72. I think it achieves this outcome at the moment, but if you could check it would be much appreciated.
Bank deposits are used to fund the property purchases once the available funding from the fund has been used up. That means the $300,000 purchase is funded by the bank and the property portfolio is used as security for the purchase. There is currently an if statement in row 41 that shouldn't need any alteration if the formula in row 40 is correct.
No change required, the calculation is sourced from the Input - Expenses sheet and can be manually altered.
No change required, the calculation is sourced from the Input - Expenses sheet and can be manually altered
Utilities, rates, Taxes
This is the only calculation that is required in this category. We need to calculate land tax at the rate of (50% of property purchase price x 1.6%). The land tax becomes payable at the 12 month period based on the number of properties being held. If the cumulative value of the land being held in December is $1,000,000 then the land tax payable will be $16,000 and will be payable in January the following year.
If you could insert a land tax input to calculate the tax payable each year, that would be great.
Council Rates, Water Rates & Insurance - Can be deleted as they aren't required
This is the amount of funding supplied by the fund. The calculation appears to be fine at present and doesn't require altering.
This is the first funds that are used when available before any bank funding is drawn. The formulae in row 66 seems to be fine at present and performs that function correctly. Once the money available ends, the model then starts drawing from the bank Phase 1 funding and then Bank Phase 2 funding if applicable.
The incomes received by the fund are put back into the loans/fund in the following order:
1. Bank Funding Phase 2
2. Bank Funding Phase 1
3. Fund Funding
The formulaes in rows 67, 73 & 80 all seem to work ok and work in that order.
Once the income is received, it becomes available to redraw again for the purchase of more houses.
The available balances are to work as follows:
The Fund Funding available balance we would like to be able to alter on a monthly basis. So if the total investment funds received is $5,000,000 we are able to hold aside a cash reserve companent of funds for people wishing to redeem their investment. The total held aside should be an input that is able to be altered manually.
The Bank Phase 1 available balance should be a balance that is able to be drawn (principal amount) without the inclusion of interest charges. At the moment, I'm not sure whether the calculation is allowing the principal to be drawn in full and then adding the interest on top of the drawdowns. The fund has plently of serviceability to pay the interest back each month as it falls due.
Bank Phase 2 is the same as point 2 above. They are both getting their available balances for borrowing from D8 & D9 of the Inputs - Expenses Sheet.
Is the limit of the funding available. It doesn't affect any of the calculations so there no changes are required to it.
I have inserted some data validation drop down list for cell D84. The objective was an attempt to be able to calculate the Internal Rate of Return for the investment over a period of time. The issue is that in order to achieve that, the outstanding value of each house purchased needs to be added back. That is Purchase price+Mark-up less purchaser deposit less principal paid by purchaser. Not sure how this could be done or if you are able to get that functionality to work?
Total Income and Expenses
Both seem to work fine, have used the sum, index and match functions to calculate the income and expenses at the month shown in the drop down list.
Internal Rate of Return
Would like a table that shows the IRR for the investment based on the number of months shown in the drop down list at cell D84. The investors all like to know what the IRR on their investment is and we obviously like to know this when we are making presentations to the investors.
Lastly, I would like to have the spreadsheet have a function that enables it to purchase properties at the maximum rate possible for the amount of cash available in the fund and the loan. The function should also give the user an input to limit the maximum number of properties purchased in a month so the timeline can be made realistic.
That means when a property completes or is rescinded, excel automatically buys a property the next month with the proceeds available, utilising as much of the available funding as possible.
We would also like the spreadsheet to calculate a way of automatically completing the contract in row 3 when the capital gain rate + purchaser deposit + principal received = 10% of the purchasers purchase price (Base Property Price + 7.5% mark-up margin)
I know there are many requests in here, however I am happy to pay you accordingly if you are able to get the spreadsheet to carry out all those functions. I don't have the VBA programming skills to enable all those functions to work.
Được trao cho:
74 freelancer đang chào giá trung bình $358 cho công việc này
Dear wardy0601, I want to do this "Excel Financial Spreadsheet" project now, let's start immediately. I have read the job description. Please send me more details of the job. Regards, Oliver