Precision Financial Calculator

Version 6.1 August, 2002

Contents

About: The document you are reading here contains only portion of the user's manual. We constantly update and enrich this document. You can find the latest version from our web site or from the latest version of our software installation.

User's Manual: All the financial formulas used in the Precision Financial Calculator are derived and explained in details in the user's manual. The book will also give you an in-depth understanding of common financial calculations and is worth to keep. To order our products, please go to our web site at http://www.DataDynamica.com or click the hyperlink from the Help menu in the program.

Examples: More useful examples will be added in the future releases.

Using Precision

Investment Analysis
Exact Date Cash Flows (XIRR)
Project Economic Evaluation Worksheet
Multiple Worksheets Cash Flows Import
How Do I ....
Copy data to Excel, Word
Copy data from Excel
Copy charts and graphs

Examples

Example 1-1 Calculate NPV, IRR, FV from Cash Flows
Example 1-2 Investment Comparisons
Example 1-3 How IRR and MIRR apply to you
Example 1-4 Timing makes a difference on NPV
Example 1-5 Why you got a different NPV using Excel
Example 1-6 Calculate Rate of Return from accounting records
Example 1-7 Simple Future Value calculation
Example 1-8 A complicated project economic evaluation
Example 2-1 Depreciation Calculation
Example 3-1 Mortgage payment and refinance
Example 3-2 Additional Principal Payments
Example 3-3 A Thousand-Year Loan
Example 3-4 Effective APR
Example 4-1 Inflation Calculation
Example 5-1 Annuity Calculation - 401(K) Savings
Example 5-2 Annuity Calculation - Distributions
Example V-1 Verify IRR
Example V-2 Verify Reinvestment Interest Rate
Example V-3 Verify different period duration
Example V-4 Investment and Loan: Two sides of a coin

Glossary

Depreciation: ADS Conventions GDS MACRS Methods
Investment: APR BCR FV IRR MIRR


Investment Analysis

Investment

The only thing you need to know to run the Discount Cash Flow Rate of Return Analysis (DCFROR) is to enter data and highlight the cash flows range. Type in the cash flows, negative for cash-out (costs or investments) and positive for cash-in (revenues or returns). Use the mouse left button to click on the first row, drag down and release the mouse button.

You will automatically get this screen.

Now change the Discount Rate, you will get the new results.
Watch the changes when you type in any single digit.

Go To Example 1-1
 Go To Top


Exact Date Cash Flows

Exact Date Cash Flows

Select Non-Periodic for the Exact Date Cash Flow analysis.

Enter date information on the Date column corresponding to each cash flow. Then highlight cash flows range (first column always).

Number of Periods and Payback change to Day unit for none-periodic or exact-date cash flows.

Go To Example 1-6


Investment Analysis

Project Evaluation Worksheet

Precision provides you with a convenient Excel worksheet template for your project economic evaluations. The cell formulas are preset, so you only need to enter your own data. You will be amazed that after entering the data, the worksheet calculates the net cash flows instantly at the end row. Then use the Paste Transpose function in the Cash Flow window, you are able to copy the net cash flow data result and paste it to the cash flow grid. Before, a single DCFROR analysis takes several hours to complete. Now using Precision you can analyze a dozen portfolios within a few minutes.

Go to the Worksheet tab and double-click or highlight the values of the cells in the blue area and change the project duration, period unit, project title and the assumed income tax rate. Select the item lines you need for your project. Then click the Worksheet button at the lower right-hand corner. If you have Excel installed on your system, Precision starts up Excel and exports a template worksheet to it. Remember, you can always change the values and the formulas from Excel.

The worksheet shown above is for the manufacturing industry. If your project is not in this category, delete the Production and Unit Price lines from the Excel worksheet. Or, uncheck these two items on the worksheet design window. Note the preset formula for each cell. Again, you can modify the titles, cell values and cell formulas at any time. You may want to paste the depreciation data from Precision's Depreciation Calculator to the Depreciation line item here. The depreciation data is to calculate the taxes. On line 13, you need to add back the depreciation to reflect the actual cash flow. The formulas do the job for you. However, to complete the calculations, you need to have some background about the depreciation, tax and all other finance related knowledge.

Generated formulas-embeded Worksheet Template

Copy Cash Flow data

Now select and highlight the Net Cash Flow line items (do not include the Total column at the end) and right mouse click to select Copy function.

To paste cash flow data, go to cash flow grid, click on the first cell, right-mouse click to pop-up the menu and then select Paste Transpose from the menu (because you want to transpose the data from horizontal to vertical).

After pasting the data to the cash flow grid, highlight the cash flow range then you will automatically get all the financial analytic results. Copy and paste data just saves your typing time. Go back to Excel to change the cell data and do the same procedures again to get another case. Before doing this, add each scenario to Summary for later cross-case comparisons. Note you can also do the Exact Date Cash Flow analysis by copying and pasting the cash flow data and the date data separately. In the Examples section, you will see a complicated project DCFROR economic evaluation example. Make your own assumptions and use this template to calculate the financial parameters of every case.

Go To Example 1-8


Investment Analysis

Multiple Cash Flows Import

At the beginning, you are required to enter the cash flow data and highlight the range to obtain the financial evaluation parameters. You might want to use copy-and-paste to save your typing time. However, you still need to do the calculations case by case.

Now Precision allows you to import cash flow data from Excel multiple worksheets. The Excel worksheet must contain the cash flows data in the same cell range on each sheet. You might have worksheets already. Go to the Worksheets Import tab and pick up a valid Excel file by clicking the Browse button or just enter the full path name of your Excel file. Tell Precision the location of the cash flow data range on each worksheet. These are the two required fields you need to fill in. Others are default values if you do not specify your values. Now you are ready to read the multiple cash flow sets, calculate and display all the results on the Summary table. Note the Summary table will be rewritten to reflect the new calculations.

Cash Flows Worksheet Import Entry Form

Once the file is specified or selected, the View button is enabled. You can click this button to open the Excel file to view it. You are able to view or even edit the Excel file contents. You can also rearrange the sheet tab order. After your changes, save the Excel file. Precision imports the most recent contents of the file. The cash flows data in the worksheet can be row-wise or column-wise. Select one from the radio buttons and enter the corresponding cell range. For the following worksheet, the cash flows is row-wise with cell range A2:D2.

Rearrange sheet tab order and define cash flow cell range A2:D2

You may type in the discount rate for all cases. Or, you may select the discount rate cell range on each sheet. The above screenshot shows the discount rate cell is A1. If you select the case title to be Auto Number, the case titles appear on the Summary Report will be Case 1, Case 2, etc.

Output to Summary Report

Click the Import to Summary button then you will get a revised Summary Report to reflect the new data from the Excel file. Precision calculates all scenarios in seconds! Starting from here you can compare the NPV, IRR and all other parameters.

Cross-case comparisons

On the Summary Report tab, select Zoom to go to a resizable window to have a better view on cross-case comparisons. Select Row mode and double-click on any applicable row to get instant chart view

Instant Comparison Chart on any financial parameter

Go To Example 1-8


Ex1-6

How Do I ...

Export Data to Excel, Word ...
Import Data from Excel
Export Charts and Graphs

Export Data to Outside

All grid or worksheet output data can be copied and pasted to other Windows applications such as Word, Excel, etc. Highlight the data range, right mouse click to pop-up menu and select Copy. Go to your Windows application, point to the desired area, right mouse click to pop-up menu and select Paste. You are pasting the data. For text boxes, if pop-up menu is not available, use Ctl-C to copy. You may use Ctl-V to paste.

Top

Import Data from Outside

There are only two places you can copy data from outside Windows applications and paste to Precision. One is on the cash flows grid, the other is on the inflation data. From your Windows application, use Copy menu function to copy data to the clipboard. Activate Precision, point to the desired place, right mouse click to pop-up menu, then select Paste. To copy a horizontal cash flows data and paste to the Cash Flow grid, use Paste Transpose sub menu. Go to Worksheet example to see more details on how to use Paste Transpose.

Top

Export Charts and Graphs

You can paste a chart's data or image generated from Precision into another Windows application. Use Alt-PrintScrn keys to copy the whole chart window screen. Go to your favorite Windows application and paste the picture (some applications allow you to use Ctrl-V short cut key). Press the Alt and PrintScrn at the same time. The PrintScrn key locates at the right hand side of the top row on your keyboard.

Every chart in Precision has a copy function. Right mouse click to pop-up menu and select Copy. Since both the data and the image of the chart are stored on the clipboard, what gets pasted into the new application varies depending on the type of application. For example, if you use the chart's Copy function and then go to an Excel spreadsheet and select Edit Paste, the chart data set is placed in the spreadsheet. To insert the image of a chart into the spreadsheet, select Edit Paste Special and select the Picture type.

Top


Ex1-1

Example 1-1 Calculate NPV, IRR, FV from Cash Flows

Mr. Thompson was offered a join venture opportunity. The project will last for five years. In the first year, Mr. Thompson put $50,000 into the joint venture fund for business operations. From the second year, the project is expected to start receiving revenues from production. Mr. Thompson’s share of revenues will be $10,000, $15,000, $20,000, and $20,000 each respective year. Assuming the current inflation rate is 3.82% each year, what is Mr. Thompson’s Return of Investment and Net Present Value for this project? What is the net value at the end of the project?

[Answer: IRR=10.14%; NPV=$8,636; FV=$10,033]

[Procedures]

In the Cash Flows worksheet, type in -50000, 10000, 15000, 20000, 20000 from row 1 to row 5 respectively. Click row 1, keep on pressing left mouse button, drag down until row 5. You will see the IRR=10.14%. Next, enter 3.82 in the Discount Rate box. You get new NPV and FV instantly.


Ex1-2

Example 1-2 Investment Comparisons

Mr. Thompson is thinking of investing his money into a mutual fund. The return of the mutual fund varies as the stock market fluctuates. However, Mr. Thompson was told that the investment rate of return would be between 10% and 15%. If he invests $50,000 this year, most likely he will get $69,000 at the end of the fifth year. What is Mr. Thompson’s true internal rate of return? When is the break even point? Which is a better investment, this or Example 1-1?

[Answer: IRR=8.39%; NPV= $9,392; FV=$10,911, Payback=4.72 years]
Example 1-1 has higher Rate of Return (10.14%), faster Payback (4.50 years), but lower Net Present Value ($8,636).

[Procedures]

In the Cash Flow worksheet, type in -50,000, 0, 0, 0, 69,000 from row 1 to row 5. Click row 1, keep on pressing left mouse button, drag down until row 5. You will see the IRR=8.39%. Next, enter 3.82 in the Discount Rate box. You will get NPV and FV.


Ex1-3

Example 1-3 How IRR and MIRR apply to you

Continue from Example 1-1. What if Mr. Thompson uses his revenues from the join venture project to invest in another project?

[Solution]

This is a realistic problem since nobody keeps substantial amount of cash in the drawer for a long period of time. He must either spend it or save it in his bank account. In addition, this problem also reminds us not to be fooled by the financial parameters obtained from the Discounted Cash Flow ROR (DCFROR) analysis. The answer: If Mr. Thompson does keep his revenues from the project in the drawer year after year, the net equivalent NPV would be $5,949 instead of $8,636 as in Example 1-1. And the Modified IRR will be 6.78%. However, if he reinvests to another project with the same IRR of 10.14% (input this number in the Minimum Reinvestment Rate box), then the Modified IRR will remain the same as 10.14% and the equivalent NPV will increase to $13,338. This is an interesting financial problem worth thinking over. Try plugging in different expected reinvestment rates of return and see the different results. Also refer to Example V-2.


Ex1-4

Example 1-4 Timing Makes Difference

Continue from Example 1-1. What if Mr. Thompson invests $50,000 at the year's end and receives revenues also at the end of the following years. Will NPV change? How about IRR, FV?

[Answer: IRR=10.14%; NPV=$8,318 vs. $8,636; FV=$10,033]
Example 1-1 has higher Net Present Value ($8,636), but IRR and FV remain unchanged. Note that investment happens at the year's end. So you need to enter 0 to row 1.

[Procedures]

In the Cash Flow worksheet, type in 0, -50000, 10000, 15000, 20000, 20000 from row 1 to row 6. Click row 1, keep on pressing left mouse button, drag down until row 6. You will see the IRR=10.14%. Next, enter 3.82 in the Discount Rate box. You will get NPV and FV.


Ex1-5

Example 1-5 Why I got a different NPV from Excel

I used NPV function from Excel and found the result was different from what I got using this financial calculator. Why?

[Answer: The difference is the the way we treat the first cash flow: is it at the beginning of the period or at the end of the period?]
In this financial calculator, we treat the first cash flow as the beginning of the period. In the investment project, if the expenses are at the end of the period, you need to put a zero cash flow at the beginning.

[Procedures]

Refer to the previous example. Example 1-4


Ex1-6

Example 1-6 Calculate Rate of Return from accounting records

Suppose you have an investment account. The beginning balance at 1/1/1999 was $100,000. Within a year, you had contributions and withdrawals from this account. What is the investment rate of return for that year?

[Procedures]

From the financial calculator, select the 'Investment' tab. Select Period Type to be Non-Periodic. The beginning balance is your investment, so type in -100,000 for the first cell. Enter 1/1/1999 on the second column. Enter the amount and date for each of your contributions or withdrawals. Note that contributions are negative (since they are investments and cash out flow) and withdrawals are positive (since they are revenues and cash in flow). Enter the ending balance and 12/31/1999 to the last row. Highlight cash flow from row 1 to the last one. You will get the IRR. Precision automatically converts all IRRs to annual equivalent.

If you have the transaction amount and date data in spreadsheet format, make sure the first column is the amount and the second column is date, copy the range containing both amount and date from the spreadsheet, focus the Precision's cash flow input worksheet at the first cell, press Ctrl-V to paste the data. Precision supports Windows OLE copy and paste functionality. This is extremely useful when you have large amounts of data.


Ex1-7

Example 1-7 Simple Future Values Calculation

What is the future value of a single lump sum saving deposit of $50,000 after 5 years, assuming the interests compound monthly with APR (annual interest rate) of 6%? What is the future value of a series of monthly deposits of $800 over 5 years?

[Answer: FV=$67,442.51; FV=$56,095.10]

[Procedures]

From the financial calculator, select the 'Investment' tab. Select the investment Type to be 'Monthly' from the Type drop-down list box. Enter 6 to the Discount Rate (meaning 6% APR). Enter 50,000 to the first row of the Cash Flow and then push the Enter key. Use mouse to click at the first row, keep on pressing the mouse left button, and drag (highlight) to row 61 (60 months for 5 years). You will see from the right panel showing that the FV @6% is $67,442.51.

You can obtain the same result by using the Annuity calculator. Select the ‘Annuity’ tab from the financial calculator. On the Savings sub tab, select 'Level Deposits' as the Type of Deposit. Select ‘Monthly’ as the compound Type. Enter 50,000 to the Present Value, 0 to the Monthly Payment, 6 to the Annual Interest Rate, 5 to the Time in Years. Select Future Value as the item to be calculated. Now click the Calc button. You get the same answer.

Keep on using Annuity calculator to solve the second question. Click C button to clear all. Enter 0 to the Present Value, 800 to the Monthly Deposit Amount, 6 to the Annual Interest Rate, 5 to the Time in Years. Click Calc button. You get the future value as $56,095.10. If you select the Deposit made at the End of the period, you get a slightly less amount of $55,816.02.

Use the Investment Calculator to verify the answer. Enter 0 to row 1. Enter 800 from row 2 to row 61. Highlight the range from row 1 to row 61. This means at the beginning of the 60th month you still deposit and at the end of the 60th month, which is the beginning of the 61st month. You receive the Future Value of $55,816.02 at the end of the 60th month.


Ex1-8

Example 1-8 A complicated project economic evaluation

An investor is considering a new manufacturing project. The product is the flat screen monitor. A detailed market research has been conducted. The study projects the unit market price for the product is $300 next year, declining 10% per year in succeeding years. The project would require $4,600,000 for the main processing equipment which would be depreciated using modified ACRS 7 year life depreciation, starting with the half-year convention in year 2 when assets are placed into service. Write off the remaining undepreciated book value at year 6 which is the end of the project life. An additional development cost of $500,000 and working capital cost of $20,000 are required at year 1. Working capital is expected to return at the end of the project. The production is projected to be 8,000 units starting from year 2 and increase 15% per year. Operating expenses are estimated to be $800,000 in year 2, escalating 8% in succeeding years. Annual operating costs, including employee salary, rentals, office expenses, are projected to be $2,000,000. The effective income tax rate is 38% and minimum DCFROR is 13%. It is assumed that this project does not have other income against which to use the year 1 negative taxable income, so it must carried forward to be used against projected positive taxable income in the succeeding years. Calculate DCFROR, NPV, PWC, PWR, Benefit-Cost ratio and Payback. Can the project survive if the unit market price drops by 20% of the predicted prices?

[Procedures]

Use Precision's worksheet template and fill in the available data. Use depreciation calculator to calculate the depreciation costs and use the Copy Transpose to paste the data to the depreciation line items. Copy the net cash flow line item data from the worksheet and Paste Transpose to Precision's cash flow table. Highlight the cash flow range to get the analytical results.

For sensitivity analysis, copy the data and formulas from Sheet1 to other sheets. Change the data and get cash flow for multiple cases. Use importing worksheets functionality from the Worksheets Import tab to analyze scenarios into the Summary table and do a cross-case analysis on their NPV, IRR, Payback, etc.

Enter data to the worksheet template and add or modify the cell formulas

Copy cell formulas to other sheets and create multiple scenarios

Note the sheet tab names are the case names. Change data to get another scenario.

Go To Multiple Worksheets Cash Flows Import


Ex2-1

Example 2-1 Depreciation Calculation

You plan to buy a computer network system for a value of $100,000 for your business use. You know that this property under IRS tax laws is within the 5-year property class using double-declining switching to straight-line method. You plan to install the system in June this year. And you would like to use Mid-Quarter convention. You would like to know how much you can 'save' for your tax return from the depreciation for the first two years. At the end of the project you estimate you can sell the equipment for $10,000. How do you handle these proceeds in your project DCFROR analysis?

[Answer: $25,000 for the first year. $30,000 for the second year. ]

[Procedures]

Go to the Depreciation calculator tab. Since you are using IRS's MACRS depreciation method, select MACRS tab. Enter 100,000 to the Cost of Asset box. Select By Class tab and select 5-Year property from the drop-down list box. Select Mid-Quarter convention from the Convention drop-down list box. The Place in Service Quarter drop-down list box will pop up the available quarters. Select 2Q. Select Value from the option box. You will see the depreciation results.
For the income tax bracket of 40%, you save $10,000 and $12,000 for the first two respective years. When you sell the property at the end, it's your income.


Ex3-1

Example 3-1 Mortgage payment and refinance

Mrs. Gibson bought a house two years ago. Excluding tax and insurance fees, she is paying the mortgage $1,359.25 each month for the initial mortgage amount of $136,000 and at the interest rate of 8.75%. The term of the mortgage is 15 years. For the tax purpose, she needs to know the total interests she paid during the second year. Now the current interest rate has dropped to 7.125%. If she refinances the loan at this new rate, how much will she save from the monthly mortgage payment?

[Answer: Total interests paid in the second year = $11,300; $117.32 (the dfference between $1,359.25 and $1231.93) ]

[Procedures]

On the Loan calculation tab, type in 136,000 for the loan amount, 180 for the number of the months, 8.75 for the interest rate. Click the Calc button, you get the monthly payment of $1,359.25. From the Payment Schedule table, click and drag the mouse pointer from Interest column row 13 to row 24. You will get the sum of the interest paid for the second year. Now change the interest rate to 7.125 then click the Calc button again. You get the new monthly payment of $1231.93. Check Example 3-3 for the effective interest rate if she needs to pay an up front fee for the refinancing.


Ex3-2

Example 3-2 Additional Principal Payments

Continue from Example 3-1. With the new interest rate of 7.125%, Mrs. Gibson estimates that she can have the additional money of $45,000 to pay off the loan at the beginning of the third year (the 25th month). How many months can she shorten the loan payment? How much interest can be saved? Verify the total principal paid from the amortization table. If she decides to pay $300 each month for the additional principal payments starting from the third year, what the situation would be?

[Answer: 73 months, $45,246.54]

[Procedures]

On the Loan calculation tab, type in 136,000 for the loan amount, 180 for the number of the months, 7.125 for the interest rate. Click the Calc button, you get the monthly payment of $1231.93. The payment schedule will be displayed on the Amortization Table tab. Select the Additional Principal Payments check box. The Additional Principal Payments will show at the last column. Type in 45,000 for the additional amount to pay off at the 25th row on this column and hit Enter key. You will get the new number of payment months 107. Click on the Interest column title bar. The Interest column is highlighted. The total interest paid is $40,500.93. Compared with the original total interest $85,747.47, she saves $45,246.54. You can see the numbers from the summary chart by clicking on the chart button. Again, click on the Principal column title bar to get the total principal paid is $91,000. The sum of this number and $45,000 is $136,000.

Enter 300 to the 37th row on the Additional Principal Payments column and press Enter key. Click on this cell. Point the mouse pointer to the lower right corner of this highlighted cell, you will see the pointer icon change to cross-hair. Now drag this cross hair all the way down to the end row. You will see the term reduces to 91 months and the total interest paid reduces to $36,623.73. If you want to verify the total principal to be $136,000, take off the last additional principal payment $300 since you do not pay this amount at the end.


Ex3-3

Example 3-3 A Thousand-Year Loan

An organization wants to help your family finances. This includes a loan of ten thousand dollars with a thousand year term and at an annual interest rate of 8.0%. A loan of such a long repayment term will probably never exist. But assuming it does, will you accept it? Are you happy because they give you such a long term of years to repay the loan?

[Answer: Thanks for your generosity. But I think I can make it with a 100-year term.]

[Procedure]

On the Loan calculation tab, type in 10,000 for the loan amount, 1000 for the number of years, 8.0 for the interest rate. Click the Calc button, and you get a monthly payment of $66.67. Enter different values for the number of years and you will get the following table for the monthly payment.

Loan Term Years | Monthly Payment
1 | 869.88
3 | 313.36
5 | 202.76
10 | 121.33
20 | 83.64
30 | 73.38
40 | 69.53
50 | 67.93
60 | 67.23
70 | 66.92
80 | 66.78
90 | 66.72
100 | 66.69
110 | 66.68
120 | 66.67
200 | 66.67
1000 | 66.67
2000 | 66.67

Or, you can quickly solve this question by looking at the loan payment formula. Click the F button to see the loan formulas. If the period n becomes very large, then A/P = i. Remember you need to convert the interest rate to a monthly rate since this is a monthly payment type. 8% divided by 12 equals 0.6667%. Then A = $10,000 x 0.6667% = $66.67. There is not a penny difference in the monthly payment between a 120-year and a 1000-year loan. So why should you pay 880 more years for nothing!


Ex3-4

Example 3-4 Effective APR

You have a home mortgage of $150,000 for 15 years with an interest rate of 7.0%. The total closing cost is $3,000. What's your effective APR? If after 5 years, you buy a new home and want to pay off the balance of your first mortgage, what is your effective APR? What if you have a monthly mortgage charge of 0.25%?

[Answer: 7.326%, 7.533%, 7.814%]

[Procedure]

On the APR calculator tab (for the new version, it's under the Loan tab), type in 150,000 for the loan amount, 0.0% for the points, 3,000 for the other cost, 7.0% for the interest rate, 15 for both the Amortization Period Years and Loan Term Years. Check the Total Costs. Uncheck Mortgage Cost and Other Monthly Costs. Click the ReCalc button, you get the Effective APR 7.326%. Change Loan Term Years to 5 and click ReCalc. You get the Effective APR 7.533%. Enter 0.25% to the Mortgage Insurance and check the Insurance Cost Per Month, then click Recalc, you get the Effective APR of 7.814%.

Note the Monthly Payment for the first two cases are all $1,348.24. Also, you can see that the earlier you pay off the remaining balance the higher the effective interest rate.


Ex4-1

Example 4-1 Inflation Calculation

Assume the annual inflation rate is 3% from 1980 to 1999. What is the value of an asset in 1999 which is valued $100,000 at the beginning of 1980?

[Answer: $175,350.61]

[Procedures]

Go to the Inflation tab. Click the New button to create a new Inflation data set. Enter 1980 to the Starting Year box and 1999 to the Ending Year box. Check the Default Inf Value checkbox and enter 3 to the text box. Click the Apply button. You will see the inflation data on the left. The starting year and ending year in the calculation box are automatically set to 1980 and 1999. Enter 100,000 to the left text box, then click the right arrow button. You will see the answer. Try to select different years to get different results.

Select Mid-Year Convention if the values are measured at the middle of the year. You may edit the inflation data on the worksheet anytime and watch the instant changes on the calculation.


Ex5-1

Example 5-1 Annuity Calculation

You have a 401(K) savings plan with your current employer. You receive your paychecks twice a month. You decide to contribute 5% of your gross income to your 401(K) plan, which is equivalent to $200 per paycheck. You expect your salary will be raised 3% every year and you would like to keep the 5% contribution ratio. You select smooth growing mutual funds as your investment markets. The average annual rate of return for these funds is about 8%. Assuming you will retire 15 years later, by that time how much money will be in your account?

[Answer: $165,342.55]

[Procedures]

Go to the Annuity Savings tab. Select Percentage Increase as the Type of Deposits. Enter 3% as the increase rate per year. Select Twice a Month as the Deposit Period (24 deposits per year). Enter deposit amount $200, interest rate 8%, Number of Years 15, then click Calc button. You get the answer. Go to the Schedule tab to check how your savings grow annually. You can have a better idea by going to view the graphics. Also, from the pie chart of the Summary tab of the chart window, you will find that of this amount, almost 50% comes from the interest accrued.

Try different scenarios. For example, assume you just rolled up $25,000 to this 401(K) account from your previous employer, see how much you can have at that time. (Answer: $248,180.00)


Ex5-2

Example 5-2 Annuity Calculation: Distributions

Continue from the previous example. Assume your life expectancy is 95. That means after retirement you have 30 years you want to consume the savings. How much money can you withdraw monthly for your living assuming the interest rate is 7% after you retire?

[Answer: $1,093.65]

[Procedure]

Go to the Annuity Distribution tab. The Present Value inherits from the Savings calculation. Select Monthly as the Payoff Period. Select Amount of each Payoff as an item to be calculated. Enter 30 as the Number of Years, Interest Rate as 7%, then click Calc. You get the answer. Go to the Schedule tab and the chart window. You will see you still get a significant interest portion even if you are withdrawing money from the account.

Try different scenarios for practice. For example, assume you want to withdraw $2,000 monthly instead. How long will it last before the account is fully depleted?


ExV-1

Example V-1 Verify IRR

IRR, the Internal Rate of Return, is defined as the discount rate that generates zero net present value. Verify this by giving your own example.

[Solution]

Create yearly cash flows: -1000,300,300,300,300. This gives you IRR=7.71%. Now, type in 7.71 in the Discount Rate box. The NPV and FV at this discount rate become zero.


ExV-2

Example V-2 Verify Reinvestment Interest Rate

If you reinvest the incomes from a project at the rate of IRR of that project, what will be the results? If you reinvest the incomes from a project at the rate of a discount rate you assigned at the beginning, what will be the results?

[Solution]

Create yearly cash flows: -1000,300,300,300,300 and select 3% as the discount rate. This gives you IRR=7.71%, NPV=$115. Now, go to Reinvestment tab, type in 7.71 in the Reinvestment Interest Rate box. The MIRR will be the same as 7.71% - the net IRR will be the same if you reinvest at the same IRR rate.

Next, type in 3 in the Reinvestment Interest Rate box. The Modified NPV @ 3% will remain the same as $115 - the net NPV will be the same if you reinvest at the same discount rate.


ExV-3

Example V-3 Verify different period duration

If you have a set of cash flow, the IRR of the quarterly cash flow will be roughly four times that of the yearly cash flow. Likewise, the IRR of the monthly cash flow will be roughly 12 times that of the yearly cash flow.

[Solution]

Create yearly cash flow: -1000, 300, 300, 300, 300. This give you IRR=7.71%. Now, select Quarterly from the Period Type dropdown list box. This will change the IRR to 30.86%. Next, select Monthly. This will give you 92.57% for the IRR.

Select Period Type to Non-Periodic and type in quarterly date of 1/1/98, 4/1/98, 7/1/98, 10/1/98, 1/1/99. Check if all the parameters are still the same or very close.


ExV-4

Dr. Precision Financial Calculator

Example V-4 Investment and Loan: Two sides of a coin

If you borrow a loan from a lender, of course you want the interest rate as low as possible. However, to the lender, this business deal is an investment. The lender wants the return as high as possible. Use the loan calculator and the investment calculator to verify that the interest rate from the loan calculation is just the IRR from the investment calculation.

[Solution]

Suppose you borrow a $100,000 loan. The term is 15 years and the monthly payment is $990. Enter $100,000 for the total amount of the loan, 180 for the number of months, $990 for the monthly payment, then click RUN button. The equivalent interest rate is 8.59% APR.

Now turn to the investment calculation. Enter -100,000 to the first column (the lender invests this amount on the first month). Enter 990 from row 2 to row 181. Don't type into the cell one by one! Type 990 on the second row and hit Enter key, point the mouse pointer to the lower right corner of this highlighted cell, you will see the pointer icon change to cross-hair. Now drag this cross hair all the way down to row 181. Select Monthly as the cash flow period type. Now click on the first cell and drag down to row 181. You will see the IRR is 8.59%!


ADS

Alternative Depreciation System is one of the two systems of MACRS. ADS uses only the straight line method over a generally longer recovery period.


Conventions

MACRS simplifies the way you figure your deduction by providing three preset conventions. These conventions determine the number of months for which you claim depreciation in the year you place property in service and in the year you dispose of the property. The conventions are as follows:

  • Mid-month convention -- Use this convention for all nonresidential real property and residential rental property.
  • Mid-quarter convention -- Use this convention if the basis of property placed in service during the last three months of the tax year (excluding nonresidential real property, residential rental property, and property placed in service and disposed of in the same year) is more than 40% of the total bases of all property placed in service for the entire year.
  • Half-year convention -- Use this convention for all other property.


GDS

General Depreciation System is one of the two systems of MACRS. Generally, GDS uses the declining balance method over a shorter recovery period.


MACRS

Modified Accelerated Cost Recovery System (MACRS) is a property depreciation system adopt by Internal Revenue Service (IRS). MACRS consists of two systems that determine how you depreciate your property. The main system is called the General Depreciation System (GDS) and the second system is called the Alternative Depreciation System (ADS).Generally, GDS uses the declining balance method over a shorter recovery period. ADS uses only the straight line method over a generally longer recovery period.


Methods

MACRS provides three depreciation methods under GDS and one method under ADS of figuring depreciation on property:

  • The 200% declining balance method over a GDS recovery period.
  • The 150% declining balance method over a GDS recovery period.
  • The straight line method over a GDS recovery period.
  • The straight line method over an ADS recovery period.


APR

Annual Percentage Rate is the periodic rate of return times the number of periods in a year. For example, a 1% monthly return has an APR of 12%.


BCR

Benefit-Cost Ratio

Benefit Cost Ratio is the ratio of Present Worth Revenue (PWR) to Present Worth Cost (PWC).


Discount Rate

An interest or return rate to calculate the present value of a future amount.


FV

Future Value of an investment is based on a constant interest rate and the cash flow of each period (negative values for payments, positive values for incomes).

where i = Discount rate
CF = Cash Flow
j = the j th period
n = the total number of the period


IRR

The Internal Rate of Return or Rate of Return (ROR) for a series of cash flows of regular intervals. The internal rate of return is the interest rate received for an investment consisting of payments (negative values) and income (positive values). This is the rate that generates zero NPV. IRR is the root of the following equation:


MIRR

The modified internal rate of return for a series of periodic cash flows is the interest rate that considers both the cost of the investment and the interest received on reinvestment of cash.


NPV

Net Present Value is the cumulative present worth of income (positive values) and a series of future payments (negative values) from a series of investment cash flow using a discount rate to handle time values of money.

where i = Discount rate
CF = Cash Flow
j = the j th period
n = the total number of the period


Payback

Payback Period

Payback Period is the time when cumulative Present Value equals zero. It is also known as the Break Even point of an investment.


PVR

Present Value Ratio

Present Value Ratio is the ratio of Net Present Value (NPV) to Present Worth Cost (PWC).


PWC

Present Worth Cost

Present Worth Cost (PWC) is the total sum of all discounted costs (negative cash flow values).


PWR

Present Worth Revenue

Present Worth Revenue (PWR) is the total sum of all discounted revenues (positive cash flow values).


XIRR

Exact date internal rate of return is the IRR for a schedule of cash flows that is not necessarily periodic.


T_231359380034

Methods

MACRS provides three depreciation methods under GDS and one method under ADS of figuring depreciation on property.
· The 200% declining balance method over a GDS recovery period.
· The 150% declining balance method over a GDS recovery period.
· The straight line method over a GDS recovery period.
· The straight line method over an ADS recovery period.


Version 6.1 August, 2002

Contents

About: The document you are reading here contains only portion of the user's manual. We constantly update and enrich this document. You can find the latest version from our web site or from the latest version of our software installation.

User's Manual: All the financial formulas used in the Precision Financial Calculator are derived and explained in details in the user's manual. The book will also give you an in-depth understanding of common financial calculations and is worth to keep. To order our products, please go to our web site at http://www.DataDynamica.com or click the hyperlink from the Help menu in the program.

Examples: More useful examples will be added in the future releases.

Using Precision

Investment Analysis
Exact Date Cash Flows (XIRR)
Project Economic Evaluation Worksheet
Multiple Worksheets Cash Flows Import
How Do I ....
Copy data to Excel, Word
Copy data from Excel
Copy charts and graphs

Examples

Example 1-1 Calculate NPV, IRR, FV from Cash Flows
Example 1-2 Investment Comparisons
Example 1-3 How IRR and MIRR apply to you
Example 1-4 Timing makes a difference on NPV
Example 1-5 Why you got a different NPV using Excel
Example 1-6 Calculate Rate of Return from accounting records
Example 1-7 Simple Future Value calculation
Example 1-8 A complicated project economic evaluation
Example 2-1 Mortgage payment and refinance
Example 2-2 Additional Principal Payments
Example 2-3 A Thousand-Year Loan
Example 2-4 Effective APR
Example 3-1 Inflation Calculation
Example 4-1 Annuity Calculation - 401(K) Savings
Example 4-2 Annuity Calculation - Distributions
Example V-1 Verify IRR
Example V-2 Verify Reinvestment Interest Rate
Example V-3 Verify different period duration
Example V-4 Investment and Loan: Two sides of a coin

Glossary

Depreciation: ADS Conventions GDS MACRS Methods
Investment: APR BCR FV IRR MIRR


Version 6.0 July, 2001

Contents

About: The document you are reading here contains only portion of the user's manual. We constantly update and enrich this document. You can find the latest version from the software installation.

User's Manual: All the financial formulas used in the Precision Financial Calculator are derived and explained in details in the user's manual. The booklet will help to provide you a thorough understanding of financial calculations and is worth to keep. To order our products, please go to our web site at http://www.DataDynamica.com or click the hyperlink from the Help menu in the program.

Examples: More useful examples will be added in the future releases.

Using Precision

Investment Analysis

Exact Date Cash Flows (XIRR)

Examples

Example 1-1 Calculate NPV, IRR, FV from Cash Flows
Example 1-2 Investment Comparisons
Example 1-3 HowIRR and MIRR apply to you
Example 1-4 Timing makes a difference on NPV
Example 1-5 Why you got a different NPV using Excel
Example 1-6 Simple Future Value calculation
Example 2-1 Mortgage payment and refinance
Example 2-2 Payoff portion of the principal
Example 2-3 A Thousand-Year Loan
Example 3-1 Inflation Calculation
Example 4-1 Annuity Calculation
Example 4-2 Withdrawal from Deposit
Example V-1 Verify IRR
Example V-2 Verify Reinvestment Interest Rate
Example V-3 Verify different period durations
Example V-4 Investment and Loan: Two sides of a coin

Glossary

Depreciation: ADS Conventions GDS MACRS Methods
Investment: APR BCR FV IRR MIRR


Version 6.0 September, 2001

Contents

About: The document you are reading here contains only portion of the user's manual. We constantly update and enrich this document. You can find the latest version from the software installation.

User's Manual: All the financial formulas used in the Precision Financial Calculator are derived and explained in details in the user's manual. The book will help to provide you a thorough understanding of financial calculations and is worth to keep. To order our products, please go to our web site at http://www.DataDynamica.com or click the hyperlink from the Help menu in the program.

Examples: More useful examples will be added in the future releases.

Using Precision

Investment Analysis
Exact Date Cash Flows (XIRR)
Project Economic Evaluation Worksheet
Multiple Worksheets Cash Flows Import
How Do I ....
Copy data to Excel, Word
Copy data from Excel
Copy charts and graphs

Examples

Example 1-1 Calculate NPV, IRR, FV from Cash Flows
Example 1-2 Investment Comparisons
Example 1-3 HowIRR and MIRR apply to you
Example 1-4 Timing makes a difference on NPV
Example 1-5 Why you got a different NPV using Excel
Example 1-6 Calculate Rate of Return from accounting records
Example 1-7 Simple Future Value calculation
Example 1-8 A complicated project economic evaluation
Example 2-1 Mortgage payment and refinance
Example 2-2 Additional Principal Payments
Example 2-3 A Thousand-Year Loan
Example 2-4 Effective APR
Example 3-1 Inflation Calculation
Example 4-1 Annuity Calculation - 401(K) Savings
Example 4-2 Annuity Calculation - Distributions
Example V-1 Verify IRR
Example V-2 Verify Reinvestment Interest Rate
Example V-3 Verify different period duration
Example V-4 Investment and Loan: Two sides of a coin

Glossary

Depreciation: ADS Conventions GDS MACRS Methods
Investment: APR BCR FV IRR MIRR


Investment Analysis

The only thing you need to know to run the Discount Cash Flow Rate of Return Analysis (DCFROR) is to highlight the cash flows range. Type in the cash flows, negative for cash-out (costs or investments) and positive for cash-in (revenues or returns). Use the mouse left button to click on the first row, drag down and release the mouse button.
You will automatically get this screen.
Now change the Discount Rate, you will get the new results.
Watch the changes when you type in any single digit.