Course: Advanced Excel
Topic: Preparing Investment and Financial Comparison Models
Course Link: Advanced Excel Course – JustAcademy
Demo Link: Register for a Course Demo
Official Website: JustAcademy Homepage
Investment and financial comparison models are structured Excel worksheets used to evaluate different investment opportunities, compare financial alternatives, estimate future returns, and support data-driven decisions. These models bring together investment amounts, expected cash flows, rates of return, time periods, expenses, and financial assumptions in one place.
Advanced Excel is especially useful for comparing alternatives such as fixed deposits, recurring investments, bonds, business projects, equipment purchases, rental properties, and other cash-flow-generating opportunities. A well-designed model allows users to change assumptions and immediately see how the results are affected.
Financial models are based on assumptions and estimates. Actual returns may differ because of market conditions, taxes, fees, inflation, changing interest rates, and investment risk. Excel can support analysis, but its outputs should not be treated as guaranteed outcomes.
| Term | Meaning | Example |
|---|---|---|
| Initial Investment | The amount invested at the beginning. | ₹1,00,000 |
| Cash Flow | Money received or paid during a particular period. | Annual income of ₹15,000 |
| Return | The gain or loss generated by an investment. | Profit of ₹12,000 |
| ROI | Return on Investment, commonly expressed as profit divided by initial investment. | 12% profit on ₹1,00,000 |
| CAGR | Compound Annual Growth Rate, the annualised growth rate between two values over a period. | Growth from ₹1,00,000 to ₹1,50,000 over 5 years |
| Present Value | The current value of future cash flows after accounting for a discount rate. | Today's value of a future payment |
| Future Value | The estimated future value of an amount invested or saved at a specified rate. | Value after 5 years of compounding |
| NPV | Net Present Value; present value of future cash flows minus the initial investment. | Value created by a project after discounting |
| IRR | Internal Rate of Return; the discount rate that makes the net present value of cash flows equal to zero. | Estimated project return based on its cash flows |
| Risk | The possibility that actual results will differ from expected results. | Market value falling below expectations |
| Liquidity | How easily an investment can be converted into cash without a significant loss in value. | Cash savings versus an asset that takes months to sell |
Before entering formulas, organise the workbook into logical sections. This makes the model easier to understand, audit, and update.
| Worksheet | Purpose | Typical Contents |
|---|---|---|
| Inputs | Stores assumptions used in calculations. | Investment amount, rate, tenure, fees, growth assumptions |
| Investment Data | Stores the information for each alternative. | Investment names, initial costs, cash flows, expected values |
| Calculations | Calculates returns and financial indicators. | ROI, CAGR, NPV, IRR, net profit |
| Comparison | Summarises alternatives side by side. | Returns, costs, risks, time horizon, liquidity |
| Dashboard | Communicates important results visually. | Charts, key metrics, scenario summaries |
Suppose a business wants to compare three hypothetical investment alternatives. The first step is to organise the available information into a structured table.
| Investment | Initial Amount | Estimated Value After 3 Years | Estimated Profit | Simple Total Return |
|---|---|---|---|---|
| Investment A | ₹1,00,000 | ₹1,30,000 | ₹30,000 | 30% |
| Investment B | ₹1,20,000 | ₹1,56,000 | ₹36,000 | 30% |
| Investment C | ₹80,000 | ₹1,00,000 | ₹20,000 | 25% |
These figures are illustrative and do not represent forecasts or recommendations. The simple total return compares the increase in value with the initial amount and does not account for intermediate cash flows, taxes, fees, or differences in risk.
| Column | Heading |
|---|---|
| A | Investment Name |
| B | Initial Investment |
| C | Final Value |
| D | Profit |
| E | Total Return (%) |
| F | Investment Period (Years) |
| G | CAGR (%) |
Assume the first investment is in row 2. Enter the investment name in A2, initial amount in B2, final value in C2, and investment period in F2.
Investment profit is the difference between the final value and the initial investment, before accounting for any additional costs or intermediate cash flows not included in those figures.
Formula:
Profit = Final Value - Initial InvestmentExcel Formula in D2:
=C2-B2If an investment grows from ₹1,00,000 to ₹1,30,000, the calculated profit is ₹30,000.
If the investment incurs brokerage, transaction charges, management fees, or other costs, create separate columns for those amounts.
Net Profit = Final Value + Income Received - Initial Investment - Total CostsThis formula is useful only when the final value, income received, and costs are recorded separately and consistently. Avoid deducting a fee twice if it is already included in the reported investment value.
ROI expresses the profit as a percentage of the initial investment. It provides a quick way to compare the profit relative to the amount invested.
Formula:
ROI = (Profit / Initial Investment) * 100Excel Formula in E2:
=IFERROR(D2/B2,0)Format cell E2 as a percentage. If the initial investment is ₹1,00,000 and the profit is ₹30,000, ROI is 30%.
CAGR estimates the annual compound growth rate between an initial value and a final value over a specified number of years. It is useful for comparing growth over different time periods when the investment has a starting and ending value and no intermediate cash flows are included in the calculation.
Formula:
CAGR = (Final Value / Initial Value)^(1 / Number of Years) - 1Excel Formula in G2:
=IFERROR((C2/B2)^(1/F2)-1,0)Format G2 as a percentage.
An investment increases from ₹1,00,000 to ₹1,30,000 over 3 years. The total growth is 30%, while the CAGR is approximately 9.14% per year.
CAGR is a smoothed annualised rate. It does not show the actual return earned in each year and should not be used as a substitute for cash-flow-based measures when money is added or withdrawn at different times.
Present Value (PV) and Future Value (FV) help evaluate the time value of money. Money available today may be invested to earn a return, while money received in the future must be evaluated in relation to a suitable discount rate.
Future Value estimates how much an amount may grow if it earns a specified periodic rate for a defined number of periods.
Excel Function:
=FV(rate,nper,pmt,[pv],[type])Example: calculate the future value of ₹1,00,000 invested for 5 years at an assumed annual rate of 8%, with no additional contributions and annual compounding.
=FV(8%,5,0,-100000)The result is approximately ₹1,46,933. This is a mathematical illustration based on the stated assumption, not a guaranteed investment outcome.
Present Value estimates today's value of future cash flows using a specified discount rate.
Excel Function:
=PV(rate,nper,pmt,[fv],[type])Example: calculate the present value of ₹1,50,000 received after 5 years, discounted at 8% annually.
=PV(8%,5,0,-150000)The result estimates the amount that would grow to ₹1,50,000 over five years at an 8% annual rate, subject to the assumptions.
Net Present Value (NPV) evaluates a series of future cash flows by discounting them to their present value. It is often used to assess business investments, equipment purchases, expansion projects, and other opportunities with an initial cost followed by future cash flows.
Basic Concept:
NPV = Present Value of Future Cash Flows - Initial Investment| Year | Cash Flow |
|---|---|
| 0 | -₹1,00,000 |
| 1 | ₹30,000 |
| 2 | ₹35,000 |
| 3 | ₹40,000 |
| 4 | ₹25,000 |
Assume the discount rate is 10% per year. Store the cash flows in B2:B6, with the initial investment in B2 and the year 1 through year 4 cash flows in B3:B6. Enter the discount rate in E2.
Excel Formula:
=NPV(E2,B3:B6)+B2Excel's NPV function discounts the first value in its range as a cash flow occurring one period in the future. Therefore, when the initial investment occurs at time zero, it is added separately. In this example, the formula adds the negative initial investment in B2 to the present value of the later cash flows.
A positive NPV does not guarantee success. The reliability of the result depends on the quality of the cash-flow estimates and the suitability of the discount rate.
IRR is the discount rate at which the NPV of a series of periodic cash flows equals zero. It is frequently used to assess projects where an initial outflow is followed by future inflows.
Excel Formula:
=IRR(B2:B6)This formula assumes that B2:B6 contains the initial investment and subsequent cash flows at regular intervals.
XIRR Example:
=XIRR(B2:B6,A2:A6)In this example, column A contains the actual dates corresponding to the cash flows in column B. XIRR calculates an annualised return based on the timing of those dated cash flows.
When comparing alternatives, the initial investment alone is not enough. A useful comparison should include cash-flow timing, total costs, expected income, investment duration, and risk.
| Criteria | Investment A | Investment B | Why It Matters |
|---|---|---|---|
| Initial Cost | ₹1,00,000 | ₹1,20,000 | Shows the starting capital required. |
| Expected Final Value | ₹1,30,000 | ₹1,56,000 | Provides a common end-value comparison when the time horizon is the same. |
| Simple Total Return | 30% | 30% | Shows the percentage change before other adjustments. |
| Investment Period | 3 years | 3 years | Ensures that the comparison uses the same time horizon. |
| Liquidity | Assess separately | Assess separately | Indicates how easily funds can be accessed. |
| Risk | Assess separately | Assess separately | Helps identify uncertainty and possible loss. |
| Fees and Taxes | Estimate or record | Estimate or record | Helps calculate more realistic net returns. |
The figures above are illustrative. When alternatives have different cash-flow dates or additional contributions and withdrawals, use NPV, IRR, or XIRR as appropriate rather than relying only on simple ROI.
A weighted comparison model can combine several criteria into a single score. It is useful when the decision depends on more than financial return, such as liquidity, cost, risk, flexibility, or time commitment.
| Criterion | Weight | Scoring Approach |
|---|---|---|
| Expected Return | 35% | Higher comparable return receives a higher score. |
| Risk | 30% | Lower risk may receive a higher score when appropriate to the decision. |
| Liquidity | 15% | Easier access to funds receives a higher score. |
| Costs | 10% | Lower fees and expenses receive a higher score. |
| Flexibility | 10% | More suitable withdrawal or adjustment terms receive a higher score. |
| Total | 100% | Weights should add up to 100%. |
The weights are examples only. They should reflect the decision-maker's priorities and should not be treated as a universal ranking method.
Assign a score from 1 to 5 for each criterion, where 1 means less suitable and 5 means more suitable. Ensure that the score direction is consistent. For example, a higher risk score should represent lower or more manageable risk if the model rewards higher scores.
| Column | Content |
|---|---|
| A | Investment Name |
| B | Return Score |
| C | Risk Score |
| D | Liquidity Score |
| E | Cost Score |
| F | Flexibility Score |
| G | Weighted Score |
Store the weights in cells B2:F2 on a separate criteria table, and place the scores for the first investment in B5:F5.
Excel Formula:
=SUMPRODUCT(B5:F5,$B$2:$F$2)Format the result consistently and copy the formula down for other alternatives. This score is a structured comparison aid, not a guarantee that the highest-scoring option is the most suitable investment.
What-If Analysis allows users to test how changes in assumptions affect financial results. This is especially useful when future returns, costs, inflation, and interest rates are uncertain.
Scenario Manager stores multiple sets of assumptions so that users can compare outcomes without manually replacing inputs each time.
| Scenario | Assumed Annual Growth | Investment Period | Purpose |
|---|---|---|---|
| Conservative | 4% | 5 years | Tests a lower-growth assumption. |
| Base | 7% | 5 years | Tests the main planning assumption. |
| Optimistic | 10% | 5 years | Tests a higher-growth assumption. |
These rates are examples for spreadsheet practice, not predictions of future performance.
Excel Data Tables show how changing one or two inputs affects a formula result. They are useful for sensitivity analysis when comparing financial outcomes under multiple assumptions.
A two-variable table can compare how both the growth rate and investment period affect the estimated future value.
A financial dashboard presents important financial metrics in a concise and visual format. It allows users to compare alternatives without reading every formula in the workbook.
| Chart Type | Best Use |
|---|---|
| Column Chart | Comparing profit or final value across alternatives. |
| Bar Chart | Comparing several investments with long names. |
| Line Chart | Showing investment value or cash flow over time. |
| Stacked Column Chart | Showing components that add to a total, such as investment cost and accumulated gains where the data structure supports it. |
| Scatter Chart | Exploring relationships between two numerical variables. |
A financial comparison model must be checked carefully because a small formula error can change the conclusions.
IFERROR carefully; do not hide errors that need to be investigated.| Error | Possible Cause | Solution |
|---|---|---|
| ROI shows an incorrect percentage. | The initial investment or profit formula is incorrect. | Verify that ROI equals profit divided by the initial investment. |
| CAGR returns an error. | Initial value is zero or negative, final value is invalid, or period is zero. | Check the input values and ensure the calculation is appropriate for positive start and end values. |
| NPV seems too high or too low. | The initial cash flow may have been included incorrectly in the NPV range. | For periodic cash flows, discount future cash flows with NPV and add the time-zero cash flow separately. |
| IRR returns an error. | The cash flows may not contain suitable positive and negative values, or the calculation may need a different guess. | Review the cash-flow series and, if appropriate, provide a guess argument to IRR. |
| Two investments cannot be compared fairly. | The time horizon, fees, or cash-flow assumptions differ. | Standardise assumptions or use cash-flow-based measures that account for timing. |
| Dashboard does not update. | Charts or formulas may not include the full data range. | Check references and use Excel Tables or appropriate dynamic ranges. |
| Formula returns #DIV/0!. | A denominator is zero or blank. | Validate inputs and use a suitable error-handling formula. |
| Results appear overly precise. | Displayed precision does not reflect uncertainty in assumptions. | Format outputs sensibly and clearly label estimates. |
Exercise: Create an Excel workbook to compare three hypothetical investments over five years.
| Investment | Initial Amount | Final Value | Period |
|---|---|---|---|
| Investment A | ₹1,00,000 | ₹1,45,000 | 5 years |
| Investment B | ₹1,50,000 | ₹2,10,000 | 5 years |
| Investment C | ₹80,000 | ₹1,08,000 | 5 years |
Build a financial dashboard for a business evaluating several possible investment projects. The workbook should combine quantitative measures with qualitative considerations to support a transparent comparison.
After completing this project, learners should be able to organise financial data, apply advanced financial functions, compare cash-flow-based investments, test assumptions, audit formulas, and communicate results using a professional Excel dashboard.
Answer: It is a structured spreadsheet used to compare financial alternatives using factors such as initial cost, projected cash flows, returns, expenses, timing, and risk.
Answer: ROI measures profit relative to the initial investment, while CAGR estimates the annualised compound growth rate between an initial and final value over a period.
Answer: NPV discounts future periodic cash flows to their present value using a specified rate. A time-zero initial investment is normally added separately to the result.
Answer: IRR is the discount rate that makes the net present value of a series of periodic cash flows equal to zero.
Answer: XIRR is appropriate when cash flows occur on specific dates that are not evenly spaced. It accounts for the actual dates in its annualised return calculation.
Answer: ROI does not automatically account for time, risk, liquidity, taxes, fees, or the timing of cash flows. A higher simple return may not represent a better overall choice.
Answer: Sensitivity analysis examines how changes in inputs, such as growth rates or costs, affect a model's output.
Answer: Scenario Manager stores alternative sets of assumptions and helps compare their effects on selected result cells.
Answer: Fees and taxes can reduce net returns. Including relevant costs helps make comparisons more realistic and consistent.
Answer: A financial dashboard presents important metrics and trends using summary figures, charts, and other visual elements to make a model easier to interpret.
Preparing investment and financial comparison models in Advanced Excel helps learners turn financial information into structured, useful analysis. By applying formulas such as ROI, CAGR, FV, PV, NPV, IRR, and XIRR, users can compare alternatives, examine cash flows, and test different assumptions.
Combining these calculations with What-If Analysis, validation checks, and clear dashboard visuals makes the workbook easier to maintain and explain. The quality of a financial comparison depends on the accuracy of its data, the suitability of its assumptions, and a clear understanding of each metric's limitations.
