Formula
Strip
The following information is located above the spreadsheet grid.
This is the name MasterPlan uses to locate the Investment Model in the Library. It is the name you will see when you search through the library to locate a particular model.
Click on the down arrow in this field to Select or Change a Category from the following list
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Enter the maximum number of years that this investment will last. If you are modeling an event that will terminate before the client's life expectancy, then remember to show the net worth returning to the client on the Cash Distribution Life (if applicable).
For some investments, you might want to define this to the client's life expectancy. The maximum number of years is 99.
The Prorate field applies to the Cash Distributions and the Tax Consequences of owning this investment model.
In other words, if the clients buy on a partial year basis, do they get the full year's amount worth of Cash Distributions, of Ordinary Income/Loss, of Portfolio Income/Loss, or of Passive Income/Loss for that partial first year? If you check this box, MasterPlan divides the total by 12 (months) and multiplies by the number of months the clients have owned the investment to arrive at the first year benefits. Only Year 1 is prorated!
However, Capital Contribution, Capital Gain/Loss, Rehab/Low Income Tax Credit, State Credits, Value of Investment, and Death Benefit are not prorated. The amount it takes to buy one unit is set.
Enter the per unit figure if you are going to run a current month Balance Sheet rather than an end-of-year Balance Sheet.
When printing a current Balance Sheet, MasterPlan will not look at the Value of Investment row (Row 13) on the Investment Model because Value of Investment is only an END-OF-YEAR figure. For assets that appreciate at a fairly rapid rate, you will have a significant difference between the first part of the year and December. Your difference is not going to be as significant between November and the end of the year.
Most MasterPlan users leave Current Balance set to the most common balance, a sort of average balance. But if you are going to be running a Current Balance Sheet for someone, then you may want to fine tune it. To do this, retrieve the model and change the amount in the Current Balance field.
Face Value is only for a Life Insurance policy.
This number does not affect the client's present portfolio.
If you want to illustrate a client buying into this investment as part of your what-if recommendations, enter the minimum number of units that a client must buy to invest.
For example, if one unit costs $1,000, and the minimum units is 2.5, MasterPlan will only show the clients investing if they have $2,500 in that year. If they have only $2,400, MasterPlan will not purchase any units.
Note: However, even if you have defined a minimum buy-in as 2.5 units, a client who already owns this investment can own only .5 or a unit (or less).
Once the clients have enough money to buy into this investment, what is the minimum number of increments that they can purchase?
For example, for the same illustration immediately above, if the clients can purchase in increments of .5, the clients can purchase 3 units, if they have not just $2,500 available, but $3,000.
If they have $3,500 available, they can purchase the original 2.5 units, plus two half units, for a total of 3.5 units.
When you click on a cell in the Spreadsheet section, MasterPlan displays the contents of the cell in the white data entry field above the years. We call this the Formula Strip.
|
Formula |
|
In this case, it is displaying a constant, but if you have entered a formula, it will display the formula in the Formula Strip, and the numeric result of the formula in the cell.
Every figure you enter here in each cell is per unit. MasterPlan will multiply the number of units the client owns by the amount in each cell.
Cells are identified by the intersection of the row and the column. The thick black line around the cell in the figure above indicates that the user just clicked on and selected Cell A1. Year 1 is Column A; Year 2 is Column B, etc. So the cell in Row 1, Column 2 is Cell B2.
If you know how to use Microsoft's® spreadsheet Excel, then you already know how to enter data here and how to identify cells.
You can enter a cell address into a formula by typing it or by clicking on the appropriate cell.
You can type numbers or enter formulas into an Investment Model Cell. You can also use MasterPlan's special function keys.
You cannot type character data into a cell. MasterPlan picks up numbers or formulas from these cells when it is creating reports.
At the bottom of the Investment Model window, MasterPlan reminds you of some of the function keys you can use. You may need to use the vertical Scroll Bar to see this land, if your monitor is small and/or you are displaying very large characters on your window.
To edit an existing formula, you can click on the cell, then click into the Formula Strip, or you can click on the cell and press the F2 function key.
Once you have typed a value in a cell, you can click or Tab into the cell immediately to the right (in the next column). Then you can press the F6 key.
For example, if you want to escalate, depreciate, or repeat the value in Cell A2, click into Cell B2 and press F6. Notice that the cell is identified to the left of the Formula Strip as being Cell B2.
When we click OK, MasterPlan will repeat the $50 for 8 additional years.
If you press the F6 key while in Year 1, MasterPlan displays this message.
If you click on a cell and Year 2 or beyond, but the cell immediately to the left is empty, then you get this message.
If you want to copy a formula (as opposed to a value) for a certain number of years, press the F7 key. In the example below, we clicked on Cell C2 and pressed the F7 key. If we leave the number of years as 0, then the formula will be copied on the entire row.
If you begin to enter a formula and get lost in the middle of it, just press the Escape key on the keyboard to clear your interim work.
To begin entering a formula, click on a cell and type the = sign.
|
Operator |
Meaning |
Operation |
Examples* |
|
+ |
Plus Sign |
Addition |
3+2; or =A1+C1 |
|
- |
Minus Sign |
Subtraction |
5-2; or =A1-B1 |
|
* |
Asterisk |
Multiplication |
5*2; or =A6*2 |
|
/ |
Forward Slash |
Division |
20/2; or =B4/B2 |
|
% |
Percent |
Percent |
20%; or =A2*20% |
|
^ |
Caret |
Exponentiation (be careful!) |
10^2 (which is the same as 10 times 10 or 100); or =A7^2 |
*We use the semicolon (;) to separate one example from the other. Be especially careful when using exponentiation, because you can easily calculate a number that cannot be displayed on reports as an integer.
If you enter a formula with several operators, MasterPlan will perform the calculations in the order listed below:
|
Sequence |
Description |
Operator |
|
1st |
- |
Negation |
|
2nd |
% |
Percent |
|
3rd |
^ |
Exponentiation |
|
4th |
* and / |
Multiplication and Division |
|
5th |
+ and - |
Addition and Subtraction |
To override or change this order of evaluation, you can enclose those parts of the formula that you want to be evaluated first.
For example, assume that we have a 5 year model. To the value of the investment for Year 1, we want to add the contribution for each year and subtract the cash distribution, growing that figure by 5%. We taxed the cash distribution as ordinary income. In Year 5, we sold the stock, paying capital gains tax on the difference between the investment in Cell A1 and the proceeds in Cell E2.
To show you the combination of formulas and constants, we checked the Show Formulas Box and scrolled horizontally, so you could see Years 2 through 5, the last year of the investment.
Notice that as you copy the formulas, MasterPlan adjusts the cell references (addresses). Notice also the use of parentheses to change the order of calculation.
In the example below, we clicked on the cell in Row 5, Column 2. Notice the tiny black square in the lower right-hand corner of the cell's border. To drag the contents of a cell, move the cursor over that tiny square. The cursor changes from the white double-cross to thin cross-hairs.
Left-click on the square and drag in the desired direction. The previous cell's contents are replicated in the direction you drag. If you are dragging a cell with a formula, the cell references are adjusted as appropriate.
If you want to move the contents of a cell, click on the cell to get the border. Then move the mouse to the middle of the bottom border of the cell.
Once you see the arrow, left-click and drag down. In this example, the contents moved from Row 5 to Row 6, once the operation was completed.
Enter the annual dollar amount per unit which the client is contributing year by year into the investment. This is out-of-pocket money.
For example, if you are defining a stock option plan, enter the strike price in this row.
For some models, you will enter an amount for only one year, probably Year 1 of the model. If the investment requires a staged pay-in, then you might enter amounts in more than one column (year).
If you have categorized the model as an investment, MasterPlan will multiply the contents of each cell by the number of units and include the totals in Line 27, Investments, on the projection reports. If this is an Income/Expense model, MasterPlan will include the totals in Line 26, Loan, Property, and Other Expenses.
Enter the annual dollar amount of projected cash flow that the client expects to receive per unit from this investment without regard to its taxability. This is strictly cash flow.
Any amounts entered here will
show up on the Cash Flow Line (Line 21) in the Baseline and/or What-If Financial Projection and Overview Reports and
add to cash flow in the Budget Worksheets.
MasterPlan does not consider cash distributions on this form to be taxable. The tax consequences of projected cash flow will be determined by what you enter in Rows 4 through 7, the Income/Loss lines. If you do not further explain the TAX treatment of this item by entering something on those lines, this cash flow will be treated as tax free.
This line relates to the Cash Distribution line above. It is asking for the dollar amount of reinvestment per unit from the cash distribution. For example: If the Cash Distribution is $100 and all of it is being reinvested, then you would enter $100 on this line (or a formula picking up the value of the cell in the row above.)
If only a portion is being reinvested, then just key the lower amount in (or use the formula). MasterPlan will treat the reinvested cash as an investment expense and the non-reinvested cash as cash available for other investments or as cash available to meet living expenses.
When calculating the Value of the Investment in Row 13 (the Net Worth), be sure to include any cash contributions and any cash being reinvested.
The amounts in Rows 4 through 7 increase or decrease (in the case of a loss) the client's taxable income.
For example, the investment might generate Phantom Income, where the clients receive no cash, but have a taxable income (or loss) item. Or they might receive a Cash Distribution which is fully taxable. So you can define situations where Cash Distribution is the same as, greater than, or less than one of the taxable income rows.
To illustrate a loss, enter a negative amount (i.e. -100).
IMPORTANT: Any entry you make in Rows 4 through 7 is only a tax item and is NOT a cash flow item. For example, if you enter $10,000 on Row 4 and show no cash distributions on Row 2, the reports will show $10,000 of taxable ordinary income but the cash flow reports will show zero. The entry on these rows is going to be treated as phantom income unless you show a cash distribution on Row 2. Conversely, if you enter a $-10,000, the reports will show an ordinary loss. In one sense, these entry items can be thought of as "tax definition" of cash distribution.
Enter any ordinary (active) income or loss per unit of this investment. Enter losses as negative numbers.
The Ordinary or Active Income definitions in the 1986 Tax Act determine how MasterPlan handles the amounts in this line.
Important: If you have selected Retirement Plan as the Category from the drop-down list, you should enter any adjustments to income on this line as a negative number to reduce the taxable income. Keep in mind that an IRA may or may not be an adjustment. If in doubt, we recommend entering the IRA in the Portfolio | Assets | Retirement Form. There we will perform the IRA deductibility calculation for you.
This is unearned income or loss that is not passive (such as income derived from stocks and mutual funds). Enter the amounts per unit of this investment; enter losses as negative numbers.
Most Limited Partnerships do not generate portfolio income; they generate passive income. However, cash and securities assets modeled as Investment Models do generate portfolio income (such as interest income and dividends).
Portfolio Income and Loss might occur when you sell the asset, or the sale might generate Capital Gain or Loss.
Portfolio Income is taxable, if the asset is not a retirement asset. If the asset is a retirement asset, it is taxable if the client is above total allowable contributions, but non-taxable if within allowable contributions (until the asset starts to pay out).
Enter any passive income or loss per unit of this investment; enter losses as negative numbers.
When MasterPlan performs the calculations to arrive at the Usable Passive Losses for the year, it will automatically use these Usable Passive Losses as deferral preference items in the Alternative Minimum Tax calculations. Keep this in mind before entering data in the Preference Item categories in the tax form and on Rows 11 and 12 in this model form.
Enter any capital gain or loss per unit for this investment. Enter losses as negative numbers.
Entries in Year 1 (column1) will be treated as short-term gains, Year 2 and beyond as long-term capital gains.
If clients own a Limited Partnership that in turn owns several properties, the clients can have a capital gain if the partnership sells a property—even though they haven't sold their share of the partnership. Generally when a partnership sells one of the properties it owns, clients will receive a Cash Distribution.
These rows allow you to enter items with special tax treatment.
If the investment or strategy you are entering includes any investment interest expense, enter that amount per unit on this row. For example, use this row if the clients get a deduction against income for the interest expense that they are paying as part of a partnership.
MasterPlan will treat any amounts you enter here according to the rules of the 1986 Tax Act.
There is no place to put an offsetting liability against a model in the model form; instead, on the Description Tab click on the Attach Liab button (or press the [F8] key). Flag it as Loan Type 'I' for Investment. Therefore, if the client is borrowing money to invest, that does NOT go into Investment Interest Expense, Row 8.
If the investment generates rehabilitation or low income housing credits, enter the per unit amount here.
MasterPlan will use these credits to offset up to $25,000 of non-passive income as long as the taxpayer's AGI, before any passive losses, does not exceed $200,000. The credit is phased out entirely (as far as non-passive income is concerned) when the taxpayer's AGI exceeds $250,000. It can still be used to reduce tax on passive income.
Enter any state tax credits that each unit of this investment may produce. These credits will be applied, in all cases, directly against the state tax due.
Enter any deferral preference income amount that each unit of this investment may produce.
The 1986 Tax Act made some significant changes in the character of certain preference items. The government has officially recognized that certain preference items do not avoid the tax—they just defer it. These kinds of preferences are called Deferral Preferences.
It is important for you to track Deferral Preferences. If your client winds up paying more Alternative Minimum Tax than table tax in any year, the difference can be used as a credit against the table tax in future years to the extent the difference was due to deferral preferences.
Basically, a deferral preference is all preference items except the following Exclusion Preferences:
Percentage depletion
Itemized deduction items
Appreciated-property charitable contribution preferences
The tax-exempt preference item
Important: MasterPlan MAY ALREADY BE CALCULATING THE DEFERRAL PREFERENCE FOR THIS INVESTMENT. Please read the paragraphs for Passive Income or Loss above. If you enter a Deferral Preference Amount here, it MAY BE DOUBLE COUNTED and result in an INACCURATE AMT (Alternative Minimum Tax) calculation.
Enter any exclusion preference income amount that each unit of this investment may produce.
Exclusion preferences are items that permanently reduce taxes. These are:
Percentage depletion
Itemized deduction items
Appreciated-property charitable contribution preferences
The tax-exempt preference item
Since the government never recaptures the tax when persons reduce their tax using one of these items, they are called exclusion preferences.
Enter here what the investment is worth per unit at the end of the year.
This is very, very, subjective. As with all other cells, MasterPlan will multiply the value per unit by the number of units your client owns to determine the total value for the end of the year.
If you do not enter an amount here, the investment's value will be zero on the Balance Sheet and on the Net Worth section of the Financial Projection Overview reports.
This is only for Life Insurance. Frequently the death benefit is different from the face value and the value of the investment, and indeed, it may be a combination of these items. If so, you may wish to use the spreadsheet functions in the Investment Model Form to accurately determine the death benefit.
Note: If this is not a life insurance policy, you can use it as a scratch pad for interim calculations, since the values on this row will not appear on any projection reports.
You can handle special expense items by entering them on the Extraordinary Living Expenses row on the Portfolio | Tax/Cash Forms | Actions | New | Cash Flow Tab form.
However, for some clients it might be convenient to show expenses such as Private School, or a Trip to Europe by naming the expense. You can create an Income/Expense Model which will handle the income or expense item for you. Select the Income/Expense Category.
The figure you enter in Row 1, Cash Contribution - Inflow, will show up on the Loan, Property, and Other Expenses row on the Projection Overview Reports.
On the Spreadsheet Tab, select the Income/Expense Category.
The figure you enter on Row 2, Capital Distribution - Outflow, will show in the Cash Flow row of the projection overview reports.
If cash flow item is taxable as ordinary income/loss, as portfolio income/loss, as passive income/loss, or as capital gain/loss, then make the appropriate entries in Rows 4, 5, 6, or 7.
For those Investment Models with a Category of Retirement Plan, MasterPlan will determine the payout by looking at Row 2, Cash Distribution. You will totally control the taxability of the cash flow, as always, by your entries in Row 4 through 7.
Remember to fill out the Retirement/Payout Tab when assigning this to a client portfolio.
You can use the Investment Model form to model a Charitable Remainder Trust.
If the income is based on an escalating or decreasing value of the trust, you can enter the value of the trust in Row 13, Value of Investment, and use that value in a formula. Later you can zero out the value in Row 13 so it will not be reflected in the client's net worth.