DIY Investment with Excel

Mastering CAGR and Holding Yield: Step-by-Step Excel Guide with RATE, XIRR Functions and ChatGPT Prompt

2025-02-20T22:03:00.000-08:00

Understanding CAGR and Holding Yield in Finance

Introduction

The Compound Annual Growth Rate (CAGR) and Holding Period Yield (HPY) are crucial metrics used to evaluate investment performance. This blog explores their importance using a practical example of an investment in a fund, examining both the CAGR and HPY to highlight their differences and significance in financial analysis.

Investment Example

An investment of $117,976 was made on October 18, 2016, and grew to $276,318.30 by February 21, 2025. The reinvestment of unit distributions is already included in the final value.

Step-by-Step Calculations Using Excel Functions

1. Calculate the Holding Period in Days

Start Date: October 18, 2016
End Date: February 21, 2025
Excel Formula: =DATEDIF("2016-10-18", "2025-02-21", "D")
Total Days: 3,047 days

2. Calculate the Holding Period Yield (HPY) The formula is: =((FV - PV) / PV) * 100

Final Value (FV): $276,318.30
Initial Investment (PV): $117,976
Excel Formula: =((276318.30 - 117976) / 117976) * 100
Result: 134.19%

3. Calculate the Number of Years (n) Convert the holding period in days into years:

Excel Formula: =3047 / 365
Result: 8.34 years

4. Calculate the Compound Annual Growth Rate (CAGR) Using the RATE function in Excel:

Syntax: =RATE(n, 0, -PV, FV)
Formula: =RATE(8.34, 0, -117976, 276318.30)
Result: 10.25%
Note: The RATE function assumes a year consists of exactly 365 days.

5. Calculate CAGR Using the XIRR Function

Create a table with dates and cash flows:
- Row 1: 2016-10-18, -117976 (initial investment, negative because it's an outflow)
- Row 2: 2025-02-21, 276318.30 (final value, positive because it's an inflow)
Excel Formula: =XIRR(B2:B3, A2:A3)
Result: 10.73%
Explanation: The XIRR function considers the actual number of days in each year (365 or 366 for leap years), providing a slightly higher return than the RATE function.

Comparison: CAGR vs. Holding Period Yield

CAGR (10.25% using RATE, 10.73% using XIRR) represents the annualized rate of return, smoothing out fluctuations and providing a clear picture of consistent growth.
HPY (134.19%) reflects the total percentage increase over the entire holding period.

The difference between the RATE and XIRR results is due to the treatment of calendar days. RATE uses a simplified 365-day year, while XIRR accounts for leap years, making it more accurate for long-term investments.

Importance of CAGR in Finance

CAGR simplifies the comparison of investment performance over different periods. Key applications include:

Evaluating Investment Performance: Provides a clear view of growth trends.
Comparing Different Investments: Standardizes returns for fair comparisons.
Forecasting Future Returns: Helps estimate potential future growth.

ChatGPT Prompt

As a certified financial planner, Calculate and compare the Compound Annual Growth Rate (CAGR) and Holding Yield for an investment initially valued at $117,976 on October 18, 2016, which grew to $276,318.30 by February 21, 2025 (including reinvested unit distributions). Provide the final comparative values for CAGR and Holding Yield rounded to two decimal places, formatted clearly in a concise table. Additionally, briefly interpret the meaning and practical implications of these two metrics for evaluating investment performance. This calculation serves as an alternative verification method to standard financial calculators and Excel.

Conclusion

In conclusion, both CAGR and HPY are essential for assessing investment performance. However, CAGR's annualized perspective makes it particularly valuable for long-term financial planning and comparative analysis, and using Excel functions like RATE and XIRR ensures accurate and efficient calculations.

Why Your Loan’s Interest Rate Isn’t What It Seems: Flat Rate vs. IRR Demystified with ChatGPT Prompt

2025-02-17T08:37:00.000-08:00

How Lenders Trick You with Low Interest Rates – The Truth About EIR and Excel Calculations

When borrowing money, many consumers focus on the advertised interest rate, often overlooking the Effective Interest Rate (EIR), which more accurately reflects the true cost of borrowing. Lenders may use marketing tactics to make loans appear more attractive by quoting nominal or flat rates instead of the actual EIR. This allows them to present lower interest rates, making the loan seem more affordable to potential borrowers. By doing so, lenders can attract more customers while obscuring the true cost of borrowing.

Borrowers who do not carefully examine the EIR might unknowingly commit to a loan with a significantly higher real interest cost than expected. For example, a lender might advertise a low flat rate of 2% per annum, which appears cheaper than a competitor’s 4% EIR, but in reality, the effective cost of borrowing could be significantly higher due to compounding interest and additional fees. Similarly, some lenders emphasize low monthly installments without highlighting the longer loan tenure, which results in higher overall interest payments.

This blog investigates the impact of EIR through four different loan scenarios.

Loan Examples, Cash Flows, and Calculation of Flat Rate vs. EIR

Example 1: Borrowing $10,000 for 1 year, Monthly Repayment of $854

Cash Flow: -$10,000 (initial) | +$854 (monthly for 12 months)
Total Repayment = $854 * 12 = $10,248
Interest Paid = $10,248 - $10,000 = $248
Flat Rate Interest = ($248 / $10,000) * 100% = 2.48%
Effective Interest Rate (EIR) computed using IRR formula: 4.64%

Example 2: Borrowing $10,000 for 3 months, Monthly Repayment of $3,374

Cash Flow: -$10,000 (initial) | +$3,374 (monthly for 3 months)
Total Repayment = $3,374 * 3 = $10,122
Interest Paid = $10,122 - $10,000 = $122
Flat Rate Interest = ($122 / $10,000) * 100% = 1.22%
Effective Interest Rate (EIR) computed using IRR formula: 7.55%

Example 3: Borrowing $10,000 for 3 years, Monthly Repayment of $302

Cash Flow: -$10,000 (initial) | +$302 (monthly for 36 months)
Total Repayment = $302 * 36 = $10,872
Interest Paid = $10,872 - $10,000 = $872
Flat Rate Interest = ($872 / $10,000) * 100% = 8.72%
Effective Interest Rate (EIR) computed using IRR formula: 5.65%

Example 4: Borrowing $10,000 for 1 year, Monthly Repayment of $881 with One-Time Processing Fee (Included in Initial Loan Amount for Accuracy)

Cash Flow: -$10,200 (initial, including $200 processing fee) | +$881 (monthly for 12 months)
Total Repayment = $881 * 12 = $10,572
Interest Paid = $10,572 - $10,000 = $572
If a one-time processing fee of $200 is included, total cost = $572 + $200 = $772
Flat Rate Interest = ($772 / $10,000) * 100% = 7.72%
Effective Interest Rate (EIR) computed using IRR formula: 6.87% (Processing fee included in initial loan amount for accurate calculation)

Key Insights

EIR is Always Higher than the Advertised Flat Rate: Lenders often quote flat interest rates, but EIR accounts for the time value of money and is always higher than the stated rate. Borrowers should always request the EIR to understand the actual cost.
Shorter Loan Terms Can Significantly Increase EIR: Although the total interest amount paid might seem low in short-term loans, the high monthly repayments can lead to an EIR much higher than the stated flat rate.
Fees and Charges Increase the Real Cost of Borrowing: Example 4 highlights how a processing fee significantly inflates the true cost of borrowing, pushing the EIR higher than expected.

How to Compute EIR and Monthly Interest Rate Using Excel for Each Example

When deciding between the IRR and RATE functions, borrowers should consider their specific loan conditions:

Choose IRR if the loan includes irregular payments or additional fees, as it dynamically accounts for the time value of money.
Choose RATE if the loan follows a fixed payment structure without additional charges, making it a simpler option for standard loans.

To calculate the Effective Interest Rate (EIR) in Excel, follow these steps for each loan example:

Choosing Between IRR and RATE Function

IRR Function: Best used when the cash flows include irregular payments or additional fees, as it accounts for the time value of money dynamically.
RATE Function: Ideal for fixed-payment loans with no additional fees, as it directly calculates the periodic interest rate based on constant cash flows.

Example 1: Borrowing $10,000 for 1 year, Monthly Repayment of $854

Enter the following values into Excel:

A1: -10000
A2: 854
A3: 854
...
A13: 854

Use the formula =IRR(A1:A13) to get the monthly IRR.
Convert it to an annual rate using (1+IRR result)^12 - 1.

Example 2: Borrowing $10,000 for 3 months, Monthly Repayment of $3,374

Enter the values:

A1: -10000
A2: 3374
A3: 3374
A4: 3374

Use =IRR(A1:A4) to get the monthly IRR.
Convert to an annual rate with (1+IRR result)^12 - 1.

Example 3: Borrowing $10,000 for 3 years, Monthly Repayment of $302

Enter the values:

A1: -10000
A2: 302
A3: 302
...
A37: 302

Use =IRR(A1:A37) to get the monthly IRR.
Convert to an annual rate with (1+IRR result)^12 - 1.

Example 4: Borrowing $10,000 for 1 year, Monthly Repayment of $881 with One-Time Processing Fee

Enter the values (including processing fee in initial loan disbursement):

A1: -10200
A2: 881
A3: 881
...
A13: 881

Use =IRR(A1:A13) to get the monthly IRR.
Convert to an annual rate with (1+IRR result)^12 - 1.

How to Compute Monthly Interest Rate Using Excel RATE Function

In addition to the IRR function, the Excel RATE function can also be used to compute the monthly interest rate and derive the Effective Interest Rate (EIR).

Example 1: Borrowing $10,000 for 1 year, Monthly Repayment of $854

Use the formula: =RATE(12, -854, 10000)
Convert to an annual rate: (1+RATE result)^12 - 1

Example 2: Borrowing $10,000 for 3 months, Monthly Repayment of $3,374

Use the formula: =RATE(3, -3374, 10000)
Convert to an annual rate: (1+RATE result)^12 - 1

Example 3: Borrowing $10,000 for 3 years, Monthly Repayment of $302

Use the formula: =RATE(36, -302, 10000)
Convert to an annual rate: (1+RATE result)^12 - 1

Example 4: Borrowing $10,000 for 1 year, Monthly Repayment of $881 with One-Time Processing Fee

Use the formula: =RATE(12, -881, 10200)
Convert to an annual rate: (1+RATE result)^12 - 1

Final Thoughts

To calculate the Effective Interest Rate (EIR) in Excel, follow these steps:

List the cash flows: Enter the initial loan disbursement as a negative value (e.g., -10,000) in one cell and the periodic repayments as positive values in the subsequent cells.
Use the IRR function: Select an empty cell and type =IRR(range_of_cash_flows), replacing range_of_cash_flows with the actual range of the cash flows.
Convert to an annual rate: Since IRR returns a monthly rate in this context, convert it to an annual rate using the formula: (1 + IRR result)^12 - 1.

Example:

A1: -10000
A2: 854
A3: 854
...
A13: 854

Formula: `=IRR(A1:A13)` then `(1+IRR result)^12 - 1`

ChatGPT Prompt

As a financial expert, calculate and compare the Flat Rate Interest and Effective Interest Rate (EIR) for a loan of $10,000 borrowed for a 1-year term, with monthly repayments of $854. Provide the summarized final values clearly in a concise table, rounding each value to 2 decimal places. Additionally, briefly interpret these two interest rate types and explain the practical differences between them to offer internal verification and insightful understanding of loan cost implications.

For the following examples, you may replace the bolded and underlined font by pasting it with another examples.

Conclusion

Understanding the true cost of borrowing goes beyond just looking at the nominal interest rate. The EIR provides a clearer picture by considering loan tenure, payment structure, and additional fees. Before signing a loan agreement, always ask for the EIR and calculate the actual repayment amount to avoid falling into marketing traps.

By making informed financial decisions, borrowers can avoid costly mistakes and select loan options that align with their financial health and objectives.

Key Takeaways for Borrowers

Understanding the difference between reducing balance and flat interest rates is crucial. A flat interest rate charges interest on the original loan amount throughout the loan term, making it appear lower but often resulting in a higher effective cost. A reducing balance interest rate, however, applies interest on the remaining principal, leading to lower interest payments over time. Always clarify which method a lender is using before committing to a loan.

Always Compare EIR, Not Just Advertised Rates – Ensure you understand the actual cost of borrowing.
Consider the Impact of Fees – Processing fees and other charges can significantly increase the cost of borrowing.
Loan Term Matters – Shorter loan durations often lead to higher EIRs.
Use Financial Tools – Excel's IRR or RATE functions can help you assess loan costs accurately.
Negotiate Terms – If possible, negotiate better terms, especially for fees and interest structures.

Unlock Financial Freedom: How Personal Financial Health Empowers Your Life

2025-02-13T00:51:00.000-08:00

Ever wondered why some people navigate life’s uncertainties—job loss, medical emergencies, or inflation—with confidence, while others feel trapped? The answer lies in one often-overlooked superpower: personal financial health. Here’s why it’s the cornerstone of a fulfilling, stress-free life (and how to start building yours today).

1. Freedom & Choices

Financial health isn’t just about numbers—it’s about empowerment. When your finances are in order, you have the freedom to say “yes” to opportunities and “no” to situations that don’t serve you.

A well-funded emergency fund allows you to leave a toxic job without fear.
Financial independence opens doors for travel, further education, or entrepreneurial ventures.
Debt freedom means keeping more of your paycheck for things that matter most.

Example: Imagine having six months’ worth of expenses saved. If you lose your job, you can take time to find the right opportunity instead of scrambling for the first available paycheck.

2. Stress Reduction & Mental Wellbeing

Money is a major source of stress. The American Psychological Association reports that 72% of adults cite finances as a top stressor. Chronic financial stress can harm relationships, sleep, and even physical health.

A “scarcity mindset” (living paycheck to paycheck) fuels anxiety and limits future thinking.
An “abundance mindset” (having financial security) fosters confidence and peace of mind.
Financial stability allows you to focus on personal growth, family, and overall well-being.

3. Compound Wealth & Future-Proofing

Building wealth isn’t about luck; it’s about consistent, small actions that compound over time.

Investing early—even small amounts—creates exponential growth.
Eliminating high-interest debt saves thousands in wasted payments.
Smart money habits today ensure a secure retirement and financial independence.

Time value of money: A 25-year-old investing $420/month at a 7% return can become a millionaire by age 65 (Excel: PMT(7%,65-25,0,1000000)/12). Waiting just ten years to start cuts that final number in half. Procrastination has a hefty price tag!

4. Resilience in Crisis

Life is unpredictable. Your financial health acts as a shock absorber when unexpected challenges arise.

A well-stocked emergency fund prevents financial disaster from medical emergencies or job loss.
Diversified income sources offer stability during economic downturns.
Smart insurance choices protect against catastrophic financial loss.

Example: When Sarah lost her job, her 6-month emergency fund turned panic into a strategic career pivot instead of a desperate scramble.

5. Legacy & Impact

Beyond personal security, financial health enables you to make a difference in the lives of others.

Provide for your family without burdening them.
Support charities, scholarships, or community projects.
Leave a lasting inheritance that ensures generational wealth.

Call to Action

Your financial health is your greatest asset—so start strengthening it today.

Audit your financial health: Calculate your net worth and track your spending for the next seven days.
Take action: Open a high-yield savings account, start an investment plan, or eliminate one financial drain from your budget.
Check out our checklist: “Mastering Personal Finance: Using Excel to Determine Asset, Liability, and Net Worth” for a step-by-step action plan.

Money is a tool, not a goal. Treat your financial health like self-care—and watch how it transforms your life.

Conclusion

Understanding the Time Value of Money (TVM) is crucial for making sound financial decisions. Every dollar you invest or save today has the potential to grow exponentially over time, reducing financial strain in the future. Proper money management ensures that you can take advantage of compound interest, avoid unnecessary debt, and maintain financial stability. By prioritizing financial health and making informed choices, you can prevent avoidable troubles, secure your future, and truly enjoy financial freedom.

Achieve Financial Clarity: Using Excel to Calculate Net Worth and Key Financial Ratios

2025-02-12T23:46:00.000-08:00

Introduction: In personal finance, understanding your asset, liability, and net worth is crucial for making informed financial decisions. Excel is a powerful tool that can help streamline this process. This guide will walk you through setting up an Excel sheet to determine your financial standing, including net investible assets, debt servicing ratio, savings ratio, and liquidity/expense ratio.

Step 1: Setting Up the Asset and Liability Worksheet

Open a new Excel spreadsheet and create the following column headers:
- A1: Asset Category
- B1: Asset Value
- C1: Liability Category
- D1: Liability Value
List all your assets (cash, investments, real estate, etc.) under column A and their corresponding values under column B.
List all your liabilities (loans, credit card debt, mortgages, etc.) under column C and their corresponding values under column D.

Step 2: Calculating Net Worth

In a new cell, use the formula:
```
=SUM(B2:Bn)-SUM(D2:Dn)
```
- This formula subtracts total liabilities from total assets to determine net worth.

Step 3: Determining Net Investible Assets Net investible assets refer to the liquid and semi-liquid assets that can be used for investment purposes.

Identify and sum up investible assets such as savings, stocks, bonds, and mutual funds:
```
=SUM(B2, B4, B5)  
```
(where B2, B4, and B5 represent rows with investible assets.)
Deduct any short-term liabilities:
```
=SUM(B2, B4, B5) - SUM(D2, D3)
```

Step 4: Computing the Debt Servicing Ratio (DSR) Debt servicing ratio helps in understanding your financial obligations relative to income.

Input total monthly debt payments in a cell (e.g., E2) and total monthly income in another (E3).
Use the formula:
```
=E2/E3
```
- A lower ratio indicates better financial health.

Step 5: Calculating the Savings Ratio The savings ratio determines how much of your income is being saved.

Input total monthly savings (F2) and total monthly income (F3).
Use the formula:
```
=F2/F3
```
- A higher savings ratio is preferable.

Step 6: Calculating the Liquidity/Expense Ratio The liquidity/expense ratio measures the ability to cover expenses using liquid assets.

Input total liquid assets (G2) and total monthly expenses (G3).
Use the formula:
```
=G2/G3
```
- A ratio above 3 indicates strong financial liquidity.

Step 7: Analyzing and Interpreting Results

If net worth is positive, financial stability is high.
A DSR below 30% is considered healthy.
A savings ratio above 20% is ideal for long-term financial security.
A liquidity/expense ratio above 3 suggests strong financial liquidity.

Complicated Net Worth Example and Analysis: Example Scenario: John has the following financial details:

Assets:
- Cash: $10,000
- Stocks: $50,000
- Real Estate: $300,000
- Retirement Funds: $100,000
- Car: $20,000
Liabilities:
- Mortgage: $200,000
- Car Loan: $10,000
- Credit Card Debt: $5,000
- Student Loan: $25,000

Using the formula:

=SUM(10000, 50000, 300000, 100000, 20000) - SUM(200000, 10000, 5000, 25000)

John’s net worth is:

= $480,000 - $240,000 = $240,000

Debt Servicing Ratio (DSR) Analysis: John’s monthly income is $8,000, and his monthly debt payments are $2,400.

= 2400 / 8000

DSR = 30%, which is at the upper limit of a healthy financial standing.

Savings Ratio Analysis: John saves $2,000 per month.

= 2000 / 8000

Savings Ratio = 25%, indicating a good saving habit.

Liquidity/Expense Ratio Analysis: John’s total liquid assets (cash + stocks) are $60,000, and his monthly expenses are $6,000.

= 60000 / 6000

Liquidity/Expense Ratio = 10, which indicates very strong liquidity.

Conclusion: By using Excel to track assets, liabilities, and financial ratios such as net worth, debt servicing ratio, savings ratio, and liquidity/expense ratio, individuals can gain deeper insights into their financial standing. Maintaining a positive net worth, managing debt effectively, saving consistently, and ensuring liquidity for emergencies are essential steps toward financial stability and growth. Mastering these formulas will empower individuals to make informed financial decisions and improve overall financial health.

Understanding CAGR, NPV, and IRR in Excel: A Practical Guide

2025-02-12T23:11:00.000-08:00

When analyzing investments, understanding key financial metrics is crucial. This guide explains Compound Annual Growth Rate (CAGR), Net Present Value (NPV), and Internal Rate of Return (IRR)—three essential financial tools. Using Excel, we'll walk through the formulas step by step, compare standard functions with their more precise alternatives (XNPV and XIRR), and highlight key cautions when using NPV.

1. Understanding CAGR (Compound Annual Growth Rate)

CAGR measures the average annual growth rate of an investment over a period of time, smoothing out fluctuations. It helps investors compare different investments with varying growth patterns.

Formula for CAGR

CAGR = \left( \frac{FV}{PV} \right)^{\frac{1}{n}} - 1

Where:

FV = Future Value
PV = Present Value (initial investment)
n = Number of years

Excel Formula for CAGR

You can use Excel to calculate CAGR using the POWER function:

=POWER(FV/PV, 1/n) - 1

Or, alternatively:

=(FV/PV)^(1/n) - 1

Example Calculation in Excel

Assume an investment grows from $1,000 to $3,500 in 5 years.
Use the formula:

=(3500/1000)^(1/5) - 1

This returns 0.284, meaning the investment grows at 28.4% per year on average.

2. NPV vs. XNPV: Key Differences and How to Use Them in Excel

Net Present Value (NPV) and Its Importance

NPV helps determine the current value of future cash flows, considering a discount rate. It tells whether an investment is profitable based on projected returns.

Standard NPV Formula in Excel

NPV = \sum \frac{C_t}{(1 + r)^t}

Where:

C_t = Cash flow at time t
r = Discount rate
t = Time period

How to Calculate NPV in Excel

Example Scenario

Assume an investment with the following cash flows:

Year	Cash Flow ($)
0	-10,000
1	3,000
2	4,000
3	5,000

With a 10% discount rate, the Excel formula for NPV is:

=NPV(10%, B2:B4) + B1

📌 Caution: Excel’s NPV function assumes cash flows occur at the end of each period. If cash flows happen at irregular intervals, the result may be inaccurate.

XNPV: A More Accurate Alternative

Unlike NPV, XNPV considers the exact timing of cash flows, making it more precise when cash flows are irregular.

Example Excel Formula for XNPV

=XNPV(10%, B2:B4, A2:A4)

Where:

A2:A4 contains the actual dates of cash flows.
B2:B4 contains the corresponding cash flows.

✔ Use XNPV instead of NPV when cash flows do not occur at regular intervals.

3. IRR vs. XIRR: Understanding the Difference

Internal Rate of Return (IRR)

IRR is the discount rate at which NPV becomes zero—in other words, the expected rate of return on an investment.

Excel Formula for IRR

=IRR(B1:B4)

Where B1:B4 contains the series of cash flows.

📌 Limitation: IRR assumes cash flows occur at regular intervals.

XIRR: A More Flexible Alternative

XIRR accounts for irregular cash flows by using exact dates.

Example Excel Formula for XIRR

=XIRR(B1:B4, A1:A4)

Where:

A1:A4 contains dates.
B1:B4 contains cash flows.

✔ Use XIRR instead of IRR when cash flows are received at different times.

4. Key Cautions When Using NPV

🚨 Avoid These Common Mistakes:

Ignoring Initial Investment – Excel's NPV function does not automatically include the initial investment. Manually add it:
```
=NPV(10%, B2:B4) + B1
```
Assuming Equal Intervals – If cash flows are not evenly spaced, use XNPV instead.
Misinterpreting Results – A positive NPV means a good investment, while a negative NPV means potential loss.

Conclusion

Use CAGR to measure growth over time.
Prefer XNPV over NPV for irregular cash flows.
Choose XIRR instead of IRR for more accuracy when cash flows vary in timing.
Always double-check assumptions to avoid errors in financial modeling.

By mastering these Excel functions, you can make more informed investment decisions and ensure accurate financial analysis.

Stock N Performance Breakdown: CAGR, NPV, IRR & Dividends Explained

2011-08-21T23:54:00.000-07:00

Ever wondered how much your investment could grow over time? Understanding CAGR and other key financial metrics can help you unlock the true potential of your portfolio. In this guide, we'll break down Stock N's performance and show you exactly how to calculate your returns, including capital appreciation, dividends, and essential financial indicators like Net Present Value (NPV) and Internal Rate of Return (IRR).

Step 1: Tabulate Your Cash Flow

To begin, record all the investment inflows and outflows associated with Stock N over the given period.

Step 2: Sum Up All Investment Outflows

The total amount invested in Stock N is calculated as follows:

$3,224.48 + $3,224.48 + $4,646.38 + $4,377.63 = $15,472.97

Step 3: Sum Up All Holding Shares

The total number of shares accumulated:

10,000 + 10,000 + 10,000 + 24,000 + 7,500 = 61,500 shares

Step 4: Calculate the Average Cost Per Share

The average cost per share is determined by dividing the total investment by the total number of shares:

$15,472.97 / 61,500 = $0.2516 per share

Step 5: Market Price Per Share

The latest market price per share is $0.530.

Step 6: Calculate Holding Return (Appreciation)

The appreciation of the stock from 2-Sep-06 to 22-Aug-11 is calculated using:

(0.53 - 0.2516) / 0.2516 = 110.65%
Alternatively, using total market value:
- (32,595 - 15,472.97) / 15,472.97 = 110.65%

Step 7: Sum Up All Received Dividends

The total dividends received over the holding period:

$290 + $386.90 + $585 + $630 + $631.80 + $891.75 + $889.25 + $889.25 + $889.25 = $6,083.20

Step 8: Calculate Holding Return (Dividend)

The return from dividends:

$6,083.20 / $15,472.97 = 39.32%

Step 9: Calculate Total Holding Return (Appreciation + Dividend)

The overall return from stock appreciation and dividends:

110.65% + 39.32% = 149.97%

Step 10: Calculate NPV and IRR

To assess the investment's financial viability:

NPV @ 15% = $7,532.12 (accepted)
IRR = 32.12% per annum

Stock N has demonstrated impressive growth over time, with a total return of 149.97% and an IRR of 32.12%. This performance outpaces many traditional investments, highlighting the importance of understanding key financial metrics when making investment decisions.

Would you like to analyze your own stock investments? Try these formulas and share your results in the comments.

Financial Planning for a 30s Unmarried Woman

2011-03-30T13:52:00.000-07:00

0) Background:

To illustrate how a plan is tailored to a working woman's future expectations, I profile a 33-year-old Miss C, unmarried Singaporean PR. She has an annual income of SGD33,000 and a CPF balance of SGD20,000. She bought a shop lot for MYR250,000 at Kampar and the property is now worth about MYR300,000.

She has saved about SGD10,000 in fixed deposits and has about SGD10,000 invested in unit trusts and cash value of MYR5,000 in participating life insurance bought from her brother.

However, she complained her expanse is too high, which the less saving can not bring her to anywhere. Luckily she has less debt: only an outstanding shop lot mortgage of MYR225,000, for which the mortgage loan is paid MYR1,744.42 in interest and principal every month (=PMT(7%/12,20*12,-225000,0)). Luckily, his brother Hui shares the burden with her about 50-50. In additional, her monthly expenses are about SGD2,342.

She works very hard in order to get more overtime allowance. However, her outflow has over taken her inflow. Luckily, special provident fund does help her to offset the deficit of the net cash flow.

She has no sense of retirement planning. However, she assumes that she will need about SGD2,300 (in today's dollars) every month to sustain her lifestyle after retirement. She also hopes to run her own business 3 years from now e.g. florist, snack stall, giftshop, child nurturing center at

Malaysia

. To do this, she figures that she will need to raise MYR80,000 (in today’s dollar).

Take assumption of 1SGD=MYR2.5.

1) Working Comment:

Miss C is too dedicated to her work, it doesn't mean bad. But, she can’t work forever like this 7*11. What-if she falls sick suffering from the critical illnesses, then her dream of having her own family or business will be jeopardized. Further more, the nature of her current job leaves her no quality time of thinking how to kick start her own business. For her age typically, she must be at least at the senior executive position, at the average monthly salary SGD3500 (total annual income divided by 12). She needs to focus on the working value that pushes her career up to another level. It is normal that she faces the political resistance as everyone does in the working life.

2) Cash flow analysis:

Miss Chas single source income, which is only from her employment.

Rental, food and shop lot loan are already consuming 68%. It is a heavy life living at

Singapore

Cost of living comparison between Singapore, Singapore and Kuala Lumpur, Malaysia

	Singapore	Kuala Lumpur	Difference
Restaurants	[Edit]	[Edit]
Meal, Inexpensive Restaurant	9.83 S$	3.86 S$	-60.74 %
Meal for 2, Mid-range Restaurant, Three-course	39.68 S$	19.96 S$	-49.69 %
Combo Meal at McDonalds or Similar	6.57 S$	4.37 S$	-33.43 %
Domestic Beer (0.5 liter draught)	7.04 S$	3.87 S$	-44.94 %
Imported Beer (0.33 liter bottle)	8.21 S$	4.76 S$	-42.01 %
Coke/Pepsi (0.33 liter bottle)	1.26 S$	0.75 S$	-40.58 %
Water (0.33 liter bottle)	1.10 S$	0.48 S$	-56.01 %

Markets	[Edit]	[Edit]
Milk (regular), 1 liter	2.88 S$	2.07 S$	-28.12 %
Loaf of Fresh Bread	2.14 S$	1.15 S$	-45.92 %
Eggs (12)	2.37 S$	1.68 S$	-29.05 %
Fresh Cheese (1kg)	8.95 S$	7.24 S$	-19.14 %
Chicken Breasts (Boneless, Skinless), (1kg)	13.58 S$	5.52 S$	-59.33 %
Water (1.5 liter bottle)	1.84 S$	0.91 S$	-50.51 %
Bottle of Wine (Mid-Range)	22.77 S$	13.99 S$	-38.54 %
Domestic Beer (0.5 liter bottle)	5.41 S$	2.85 S$	-47.38 %
Imported Beer (0.33 liter bottle)	5.89 S$	3.77 S$	-35.98 %
Pack of Cigarettes (Marlboro)	10.99 S$	3.77 S$	-65.64 %

Transportation	[Edit]	[Edit]
One-way Ticket (local transport)	1.60 S$	1.02 S$	-36.06 %
Monthly Pass	46.79 S$	32.48 S$	-30.57 %
Taxi (5km within center)	8.86 S$	3.76 S$	-57.55 %
Gasoline (1 liter)	1.79 S$	0.78 S$	-56.26 %
Volkswagen Golf 1.4 90 KW Trendline (Or Equivalent New Car)	107,626.59 S$	71,008.77 S$	-34.02 %

Utilities (Monthly)	[Edit]	[Edit]
Basic (Electricity, Gas, Water, Garbage)	155.11 S$	53.42 S$	-65.56 %
1 min. of Prepaid Mobile Tariff (no discounts or plans)	0.26 S$	0.18 S$	-33.60 %
Internet (4 Mbps, Flat Rate, Cable/ADSL)	36.69 S$	44.19 S$	+20.43 %

Sports And Leisure	[Edit]	[Edit]
Fitness Club, Monthly Fee for 1 Adult	121.40 S$	61.90 S$	-49.01 %
Tennis Court Rent (1 Hour on Weekend)	10.38 S$	8.29 S$	-20.11 %
Cinema, International Release, 1 Seat	8.99 S$	5.21 S$	-42.07 %

Clothing And Shoes	[Edit]	[Edit]
1 Pair of Levis 501	129.65 S$	104.55 S$	-19.36 %
1 Summer Dress in a Chain Store (Zara, H&M, ...)	91.94 S$	93.97 S$	+2.21 %
1 Pair of Nike Shoes	127.79 S$	92.89 S$	-27.31 %
1 Pair of Men Leather Shoes	125.23 S$	93.42 S$	-25.40 %

Rent Per Month	[Edit]	[Edit]
Apartment (1 bedroom) in City Centre	2,505.23 S$	385.89 S$	-84.60 %
Apartment (1 bedroom) Outside of Centre	1,498.61 S$	247.58 S$	-83.48 %
Apartment (3 bedrooms) in City Centre	5,399.45 S$	845.45 S$	-84.34 %
Apartment (3 bedrooms) Outside of Centre	3,308.39 S$	578.60 S$	-82.51 %

Buy Apartment Price	[Edit]	[Edit]
Price per Square Meter to Buy Apartment in City Centre	16,816.86 S$	1,684.70 S$	-89.98 %
Price per Square Meter to Buy Apartment Outside of Centre	8,549.38 S$	1,117.20 S$	-86.93 %

Salaries And Financing	[Edit]	[Edit]
Median Monthly Disposable Salary (After Tax)	3,301.10 S$	1,404.76 S$	-57.45 %
Mortgage Interest Rate in Percentanges (%), Yearly	2.43	4.56	+87.66 %

Currency : SGD
Source: http://www.numbeo.com/

3) Networth Analysis

4) Financial Ratio


*a) Liquidity Ratio* = Cash, or near cash/Monthly expenses
	8.54	Month	good
(Comment: More than meet the benchmark band of 3 – 6 months of expenses)


*b) Liquid Assets to Net Worth*
	32.22%
(Comment: higher liquidity – may be a bit easy or more flexible in converting or restructuring assets in case of an immediate investment opportunity)


*c) Solvency Ratio* = Net Worth/Total Assets
	58.56%
(Comment: Relatively high solvency. Financial stability is ranked as strong)


*d) Debt to Asset ratio* = Total Liabilities/Total Assets
	41.44%		good
(Comment: allow to be higher if risk profile permits)


*e) Saving ratio* = Annual Saving/Take Home Pay
	8.63%		no good

(Comment: far to the national saving rate of about 30%. However, in this calculation, savings in insurance policies, car and housing loan repayments on principals are excluded. If included, the cash values of such payments will add to the ratio.)


*f) Debt Servicing Ratio* = Total Serving Loan/Total Inflow
	12.68%		good

5) Future Expectation

Current Liquid Asset	SGD 20,000.00
Saving Duration	3	year
Expected Today Amount	SGD 32,000.00
Inflation	3%	p.a.
Expected Future Amount	SGD 34,967.26
Investment Rate	10%	p.a.
PMT@ROI=8%	($3,010.42)		Failed as net cash flow < PMT
PMT@ROI=10%	($2,521.83)		Accepted as net cash flow > PMT

Action Item:
1. Seek Hui to invest SGD2522 annually in UT for at least 10%p.a. return

6. Retirement Planning


Step 1(A): Retirement income needed – Target replacement ratio (TRR)
Description: Miss C, 33 years old, she earns SGD 33000 p.a. Her yearly increment is 0.03. She seeks for the feasibility of maintaining her life style during her retirement age.
Age (Now)	33
Age (Retire)	60.00
Increment	3%	p.a.
Annual salary	SGD 33,000.00

His last drawn salary			SGD 71,167.51

Assumption:
TRR(50%, e.g. less commitment)
		50%	SGD 35,583.76
TRR(60%, e.g. child educational funded by own, bad health family members etc)
		60%	SGD 42,700.51
TRR(70%, e.g. loan not clear yet, child educational funded by own, bad health family members etc)
		70%	SGD 49,817.26
Note: Higher TRR produces higher security but high cost too. Thus, another method is studies.


Step 1(B): Retirement income needed – Expenses method
Description: Miss C's outflow is SGD 28102.61 with estimated inflation rate at 0.02 p.a.
Outflow (Now)	(SGD 28,102.61)
Inflation (Low)	2.00%	p.a.

Future annual expenses at age 60 year old.
			SGD 47,967.97


Step 2: Retirement Fund calculation
Let’s take future annual expenses at age 60 year old is SGD 49817.26.
Life expectancy (Malaysian/Singaporean: 75 years old, +10 year as buffer).
Medical development human life is getting more advances. Thus, it will be reviewed from time to time .
Two approaches for the retirement fund calculation: (A) principal liquidation and (B) Principal intact.
Life (Low)	75
Life (High)	85
Life (Decide)	85
ROI	8.00%	p.a.
Inflation	2.00%	p.a.

Step 2 (A): Retirement Fund calculation – Principal Liquidation
Retirement fund (without inflation), 2Ai
			SGD 574,331.13
Retirement fund (with inflation), 2Aii
			SGD 681,896.57

Step 2 (B): Retirement Fund calculation – Principal intact.
Retirement fund (without inflation), 2Bi
			SGD 672,532.99


Retirement fund (with inflation), 2Bii
			SGD 896,710.65


Conclusion: From 2Ai, 2Aii, 2Bi and 2Bii, it is recommended 2Aii, SGD 681896.57 realistically.


Step 3: Retirement Funding Calculation
Insurance/UT agent will based on the SGD 681896.57 to work out the annual payment in order to close the deal fast.
Product ROI	10%	p.a.
No other Resource, PMT p.a.
			(SGD 5,118.96)
			Impossible!
Net Investible Income@33	SGD 20,000.00
Current portfolio return	4.00%	p.a
Improved portfolio return	10.43%	p.a	Solver solved
Retirement Fund	SGD 681,896.57
CPF	2.00%	p.a

Net Investible Income@60@4%			SGD 57,667.37
Net Investible Income@60@10.43%			SGD 291,465.05
CPF@60@2%			SGD 390,431.52
Total Retirement Fund@60@Current Return			SGD 448,098.89
Total Retirement Fund@60@Improved Return			SGD 681,896.57


Shortfall/(Excess)@4%			SGD 233,797.68

Thus, NIA equals required fund, she may stop work after 60 provided her investment return improved from 4% to 10.43%.

Understanding and Calculating CAGR Using Different Methods

2010-12-23T03:44:00.000-08:00

Introduction

The Compound Annual Growth Rate (CAGR) is a useful measure to determine the mean annual growth rate of an investment over a specified period, assuming the profits were reinvested at the end of each year. It provides a clear picture of an investment’s performance over time. Various methods can be used to calculate CAGR, including Excel's IRR, XIRR, TVM functions, and a standard mathematical formula.

Simple CAGR Calculation Example

Let’s assume you invested $10,000 in a portfolio on January 1, 2005. Over time, the investment grew as follows:

$13,000 by January 1, 2006
$14,000 by January 1, 2007
$19,500 by January 1, 2008

If you liquidate your investment on January 1, 2008, the CAGR can be calculated using the following methods:

1) Excel IRR Method

Define the cash flows, with negative values for outflows (initial investment) and positive values for inflows (returns).
Ignore intermediate portfolio growth that does not contribute to cash flow; zeros should be explicitly stated where applicable.
Use the IRR function for the selected range: =IRR(B4:B7).

2) Excel XIRR Method

Define the cash flows, with the same convention of negative for outflows and positive for inflows.
Assign transaction dates to each cash flow.
Use the XIRR function to calculate the internal rate of return based on specific transaction dates: =XIRR(B12:B13,A12:A13).

3) Excel TVM (Time Value of Money) Method

Use the RATE function with the formula:
```
=RATE(n, pmt, PV, FV, mode)
```
Where:
- n = number of years
- pmt = periodic payment (if applicable)
- PV = present value (initial investment)
- FV = future value (final value of investment)
- mode = 0 or 1 (payment timing)

4) Mathematical Formula Method

The CAGR formula:
```
CAGR = (FV / PV)^(1/n) - 1
```
Where:
- FV = final value
- PV = initial investment
- n = number of years

Real-Life CAGR Calculation

In reality, investments involve multiple transactions, such as:

Initial investment
Additional top-up investments
Dividend payouts
Bonus issues
Partial or full liquidation

Since these transactions occur at different times, the XIRR function in Excel is the most versatile method for computing CAGR. It considers the timing of cash flows, making it more precise for real-world applications.

Conclusion

Understanding CAGR is crucial for evaluating investment performance over time. While the mathematical formula provides a quick approximation, Excel’s IRR, XIRR, and TVM functions offer more flexibility for various scenarios. For real-life investment tracking, XIRR is the most reliable method as it accounts for irregular cash flows.

Mastering Financial Planning: A Comprehensive Guide to Securing Your Future

2010-11-05T23:19:00.000-07:00

Comprehensive Financial Planning Outline

Introduction

Financial planning is not just about managing money—it’s about building a future that aligns with your dreams and aspirations. Whether you’re striving for early retirement, securing your family’s financial well-being, or growing your wealth strategically, having a clear roadmap is essential. This guide will walk you through key aspects of financial planning, equipping you with insights to make confident, well-informed decisions. A well-structured financial plan brings stability, security, and peace of mind, empowering you to navigate life’s financial challenges with ease.

1. Executive Summary

A concise summary of findings, recommendations, and actionable strategies covering cash-flow management, risk/insurance, education, retirement, investment, estate, tax, and special needs planning.

2. Goals and Objectives

Defining client-specific financial goals and priorities in areas such as cash flow, risk management, education funding, retirement security, investment growth, estate distribution, tax efficiency, and special financial considerations.

3. Personal Data

Key financial institutions (banks, investment firms, estate agencies, insurance providers, etc.).
Dependent details (both official and non-official, if applicable).

4. Current Financial Situation

Analyzing net worth and cash flow efficiency through critical personal finance metrics:
- Liquidity ratio
- Liquid asset to net worth ratio
- Solvency ratio
- Debt-to-asset ratio
- Savings ratio
- Debt servicing ratio

5. Assumptions

Establishing mutually agreed-upon financial assumptions, including:
- Salary growth projections
- Retirement age expectations
- Dividend yield growth rates
- Inflation rates (official vs. lifestyle-based)
- Banking interest rates
- Expected investment returns
- Real estate investment performance
- Rental growth trends
- Insurance policy cash values
- Market valuations of assets (cars, properties, offices, etc.)
- Tax brackets and income forecasting
Sourced data from reputable references (e.g., Bank Negara Malaysia, Department of Statistics, EPF/CPF, etc.).

6. Cash-Flow Management

Implementing strategies based on detailed cash flow analysis:
- Reducing expenses through Pareto analysis and optimization
- Increasing income via diversified revenue streams
- Enhancing asset allocation between liquid and non-liquid assets
- Minimizing liabilities through structured repayment plans
Measuring progress through personal finance ratios.
Year-end financial tracking and evaluation.
Utilizing "Millionaire Next Door" principles (WAQ, UAW, AAW, PAW analysis) for wealth assessment.

7. Risk Management/Insurance Planning

Identifying and mitigating risks through tailored insurance solutions:
- Life insurance coverage analysis
- Health and hospitalization insurance assessment
- Personal accident protection
Property and liability insurance strategies (home, vehicle, professional coverage).
Consolidating existing insurance policies into a structured matrix for easy reference.

8. Education Planning

Creating an actionable timeline for education funding.
Forecasting education expenses using present-value calculations (XIRR/XNPV).

9. Retirement Planning

Structuring a retirement plan using:
- Target replacement ratio and expense methodologies
- Principal liquidation and principal-intact strategies
Factoring in life expectancy variations.
Conducting shortfall/excess analysis (NIA vs. required retirement funds).
Optimizing financial freedom age through Excel Solver modeling.

10. Investment Planning

Designing an optimized investment portfolio tailored to client-specific goals.
Allocating assets strategically to balance risk and return.
Applying Excel Solver for:
- Minimizing portfolio risk for a given ROI target
- Maximizing Sharpe ratio for optimal investment efficiency

11. Estate Planning

Developing asset distribution strategies through wills, trusts, and power of attorney.
Estimating estate planning costs (trust company fees, legal documentation, executor charges).
Ensuring statutory asset nominations (EPF Act, Insurance Act compliance).

12. Tax Planning

Maximizing tax efficiency through rebates, deductions, and credits.
Leveraging Tax Act provisions (sections 108/110) for dividend tax relief.

13. Special Needs Planning

Custom financial planning for individuals with unique needs (e.g., disabled dependents, second families, divorce financial settlements).

14. Business Continuation Planning

Implementing buy-sell agreements supported by life insurance to ensure seamless business transitions.

15. Recommendations, Implementation & Action Plan

Establishing a timeline-driven action plan with designated responsible persons (PICs).
Conducting periodic financial performance reviews to ensure ongoing alignment with objectives.

Conclusion

Your financial journey is a marathon, not a sprint. True financial success is not just about accumulating wealth but making strategic choices that align with your personal and professional aspirations. By following this comprehensive financial planning framework, you will be better equipped to manage risks, seize opportunities, and build a financially secure future. Taking proactive steps today will ensure long-term stability, allowing you to enjoy the peace of mind that comes from knowing your financial house is in order. The best investment you can make is in your own financial education—start planning today, and build the future you envision.

Mastering Portfolio Risk and Return: A Guide to Variance-Covariance Analysis and Optimization

2010-11-02T08:08:00.000-07:00

Introduction

Understanding portfolio risk is crucial for making informed investment decisions. Many investors struggle with calculating risk, so this guide provides a straightforward approach using the variance-covariance matrix. By following these steps, you can determine both expected returns and risks for your portfolio.

Theory: Portfolio Risk and Return

Portfolio theory, introduced by Harry Markowitz in 1952, emphasizes the importance of diversification to reduce risk while optimizing returns. According to Modern Portfolio Theory (MPT), the return of a portfolio is the weighted sum of the expected returns of individual assets:

where:

is the expected return of the portfolio,
is the weight of each asset in the portfolio,
is the expected return of individual assets.

Portfolio risk, measured by variance or standard deviation, is given by:

where:

is the portfolio variance,
is the variance of individual assets,
is the covariance between assets.

Diversification reduces unsystematic risk by combining assets with low or negative correlations, thereby stabilizing returns.

Step 1: Data Collection

Collect 60 months of historical prices for each security. You can download this data from Yahoo! Finance or other financial data providers.

Step 2: Calculate Expected Portfolio Return

For each individual stock: E(r,i) = Average expected return of 60 monthly data for stock i.

For the entire portfolio: E(r,p) = Expected return of the portfolio.

Example with Three Securities:

E(r,p) = 1.09% monthly

Step 3: Calculate Portfolio Risk

Portfolio risk calculation involves constructing a variance-covariance matrix and summing all its components.

Variance-Covariance Matrix Construction:

Single Stock: 1 component (1² = 1)
Two Stocks: 4 components (2² = 4)
Three Stocks: 9 components (3² = 9)
Four Stocks: 16 components (4² = 16)
Five Stocks: 25 components (5² = 25)
Six Stocks: 36 components (6² = 36)

... and so on. If you have 14 investments in your portfolio, then the matrix has: 14² = 196 components

Optimization

The expected return and risk of a portfolio calculated here can be used to optimize the return-risk tradeoff using Excel Solver. Portfolio optimization methods using Solver have been discussed in a previous section (see portfolio optimization).

Conclusion

By understanding and calculating portfolio risk using the variance-covariance matrix, investors can make more informed decisions. With tools like Excel Solver, it becomes easier to optimize risk and return, leading to a more balanced and efficient investment strategy. Mastering these calculations empowers investors to build robust portfolios aligned with their financial goals.

The Power of Early Investing: Comparing Investment Start Age & Return Rates for Maximum Wealth Growth

2010-10-31T09:01:00.000-07:00

Introduction

Investing early is one of the most effective strategies for building long-term wealth. The compounding effect allows investments to grow exponentially over time, making the starting age and the rate of return crucial factors in determining financial success. This article explores two key scenarios: the impact of different starting ages on investment outcomes and how varying rates of return affect long-term wealth accumulation.

Investment Vehicles and Annual Returns

Starting investments early is crucial for maximizing returns, thanks to the power of compounding. For this analysis, we assume a consistent annual return across different investment vehicles:

Fixed Deposits: 2.5% p.a.
EPF (Employees Provident Fund): 6% p.a.
Government Unit Trust: 8% p.a.
Unit Trust: 10% p.a.
Stocks: 12% p.a.
Optimized Portfolio: 15% p.a.

Each investor contributes $12,000 annually to their chosen investment vehicle.

Scenario 1: Impact of Investment Start Age

We analyze six investors who begin their investment journey at different ages:

James - Starts investing at 15 years old
Kevin - Starts investing at 25 years old
Lisa - Starts investing at 35 years old
Michael - Starts investing at 45 years old
Nancy - Starts investing at 55 years old
Oliver - Starts investing at 65 years old

Future Value at Age 80 with Unit Trust (10% p.a.)

James (15 years old): $19,838,724.55
Kevin (25 years old): $7,262,431.18
Lisa (35 years old): $2,658,456.90
Michael (45 years old): $956,220.38
Nancy (55 years old): $327,552.92
Oliver (65 years old): $106,678.42

Key Insights:

The Power of Compounding – The earlier an investor starts, the greater their final return. James, starting at 15, accumulates nearly 186 times more wealth than Oliver, who starts at 65.
Delayed Investment Drastically Reduces Returns – Each decade of delay significantly impacts total wealth, illustrating that waiting to invest results in massive opportunity loss.
Exponential Growth Over Time – The difference between investing at 15 vs. 25 results in nearly three times more wealth at 80, emphasizing the long-term exponential effect of consistent investing.

Starting early is the most critical factor in maximizing investment returns through compounding.

Scenario 2: Effect of Investment Returns

Different rates of return lead to drastically different outcomes due to compounding. The gap between a 2.5% return and a 15% return is significant over time.

Future Value Comparison at Age 80 for Different Returns

Assuming James starts at age 15 and invests $12,000 annually, the future values at age 80 for different rates of return for James are:

Fixed Deposits (2.5%): $506,235.48
EPF (6%): $1,902,348.32
Government Unit Trust (8%): $4,987,652.40
Unit Trust (10%): $12,487,953.86
Stocks (12%): $31,203,584.27
Optimized Portfolio (15%): $86,345,210.78

Key Insights:

Higher Returns Magnify the Power of Compounding – The difference between 2.5% and 15% return leads to a nearly 171x difference in total wealth at age 80.
Exponential Growth for Higher Returns – A portfolio with a 15% return grows at a much faster rate compared to a low-return investment.
The Trade-Off Between Risk and Return – While higher returns generate significantly greater wealth, they also come with higher risks. James should balance his return expectations with risk tolerance.

James should aim to optimize his returns while considering his risk levels to benefit from long-term compounding.

Time Value of Money (TVM) Formula in Excel

The future value (FV) of an investment considering annual contributions and compounding can be calculated using Excel’s FV formula:

=FV(rate, nper, -pmt, pv, type)

Where:

rate = Annual interest rate (e.g., 10% or 0.10 for Unit Trust)
nper = Number of years invested
pmt = Annual investment contribution ($12,000)
pv = Present value (initial investment, usually $0 for new investors)
type = 1 (for beginning of the year contributions) or 0 (for end of year contributions)

For example, for James investing $12,000 annually at 10% return from age 15 to 80:

=FV(10%, 65, -12000, 0, 1)

This formula helps investors calculate expected returns based on different rates and durations.

Conclusion

Portfolio optimization plays a crucial role in maximizing long-term wealth while managing risks effectively. As seen in this analysis, starting early and selecting the right investment vehicles significantly impact financial growth. However, simply investing is not enough—optimizing asset allocation, balancing risk with potential returns, and leveraging high-performing investments can substantially improve outcomes. James' example demonstrates that an optimized portfolio with higher returns significantly outperforms lower-yielding options over time. By strategically managing investments, individuals can secure financial stability and achieve their financial goals with confidence. This analysis highlights the critical importance of starting early and selecting investments that align with one's risk tolerance. Higher returns can accelerate wealth creation, but investors must be aware of potential risks and strive for a well-balanced portfolio. By optimizing asset allocation and consistently investing over time, individuals can secure a financially stable future and maximize their investment potential.

Portfolio Value Calculation - Using Excel Import External Data

2010-10-09T02:28:00.000-07:00

To calculate the portfolio value which consists of number of stocks, unit trust and others derivatives, and it could be in different currency, Excel provides the easiest and faster way to perform the job in less than a minute. It is done by linking the spreadsheet to external internet data, e.g. Yahoo Finance (currency, stock price), Investment-linked website, unit trust price list etc.

Step by step:
0. Create a Data Sheet. Then, tabulate the number of your unit trust or share or currency as per table below.

1. a) Excel 2003: Select a cell. On the Menu Bar, select Data - Import External Data.

1. b) Excel 2007: Select a cell. On the Menu Bar, select Data - From Web.

2. This will open the "New Web Query" dialog box. Your web page will be displayed. In the address block, manually type in e.g. http://finance.yahoo.com/q?s=5066.kl, and click the "Go" button.

3. a) Just as if you were searching for a quote on Yahoo!® Finance, type in the ticker symbol for the stock you want a quote on. This will be the ticker symbol quote that will be imported into MS Excel. On the dialog box, scroll down to the "Last Trade:" and check the check box that highlights "Last Trade", "Time", "Change", etc. Then at the bottom/right of the dialog box, click "Import". Another "Import Data" dialog box will appear, asking where you want to put the data.

3. b) If you want to extract data from a unit trust website, said Pruaccess,http://pruaccess.prudential.com.sg/prulinkfund/viewFundPricing.do. Then the result will be imported to spreadsheet as below.

4. Finally you may link the desired data to the portfolio calculation table.
5. For updating your portfolio you just need to click on the Refresh button.

Advance:
It can be used to calculate your net worth by compiling the subtraction of asset to the liability. By using this Excel template, daily calculation on the net worth can be done within a few minutes only.

Android Application FREENPVIRR 2.19 Tutorial - A Free NPV, IRR, XNPV, XIRR and Date Different Calculator

2010-09-15T08:44:00.000-07:00

1.0 BACKGROUND

In finance, the net present value (NPV) of a time series of cash flows, both inflow and outflow, is defined as the sum of the present values (PVs) of the individual cash flows. In the case when all future or expected cash flows are:
- inflow (e.g as coupons, dividend etc) and;
- outflow of cash is the purchase price (normally we use negative sign for the outflow).

NPV is a central tool in discounted cash flow (DCF) analysis, and is a standard method for using the time value of money to appraise LONG-TERM PROJECTS. Used for capital budgeting, and widely throughout economics, finance, and accounting, it measures the excess or shortfall of cash flows, in present value terms, once financing charges are met.

Figure 1: To calculate the net present value for a series of cash flows that is regularly periodic, NPV function is used. Graphical illustration of net present value NPV - total sum of discounted individual cash flow to present day with the given expected rate. And, net future value NFV - total sum of compounded individual cash flow to future end date of the project with the given expected rate.

Decision making criteria:
For NPV>0 with a given expected rate, the investment would add value to the firm, thus the project may be accepted.
For NPV<0 with a given expected rate, the investment would subtract value from the firm, thus the project should be rejected.
For NPV = 0 with the expected rate, the investment would neither gain nor lose value for the firm. We should be indifferent in the decision whether to accept or reject the project. This project adds no monetary value. And, the given expected rate is now called as IRR - internal rate of return.

Figure 2: Returns the net present value for a schedule of cash flows that is not necessarily periodic, XNPV is used. Graphical illustration of net present value XNPV - total sum of discounted individual cash flow to present day with the given expected rate. And, net future value XNFV - total sum of compounded individual cash flow to future end date of the project with the given expected rate.

By taking XNPV = 0, XIRR is defined as below:

2.0 Methodology
2.1 Net present value - NPV Android Java source code

dbnpv = 0;
            for(i=0;i<=5;i++)
            dbnpv = dbnpv + dbcf[i]/(Math.pow((1+dbrate/100), i));

            if (v==NPV)
            {
                if(dbnpv<=0)
                {
                    answernpv = "NPV = " + currency.format(dbnpv) +" , project/investment rejected.";
                    result.setText(answernpv);
                }
                else
                {
                    answernpv = "NPV = " + currency.format(dbnpv) +" , project/investment accepted.";
                    result.setText(answernpv);
                }
            }//NPV calculation closing

2.2 Internal rate of return - IRR Android Java source code

{
            {
               final double estimateddbirr = 0;

            Double dbirr = Double.NaN;

            if (dbcf != null && dbcf.length > 0)
            {
                //check if business startup costs is not zero:
                if (dbcf[0] != 0.0)
                {
                    final double noOfdbcf = dbcf.length;

                    double sumdbcf = 0.0;
                    //check if at least 1 positive and 1 negative cash flow exists:
                    int noOfNegativedbcf = 0;
                    int noOfPositivedbcf = 0;
                    for (int i1 = 0; i1 < noOfdbcf; i1++)
                    {
                        sumdbcf += dbcf[i1];
                        if (dbcf[i1] > 0)
                        {
                            noOfPositivedbcf++;
                        }
                        else if (dbcf[i1] < 0)
                        {
                            noOfNegativedbcf++;
                        }
                    }

                    if (noOfNegativedbcf > 0 && noOfPositivedbcf > 0)
                    { //at least 1 negative and 1 positive cash flow available?
                      //set estimated dbirr:
                        double irrGuess = 0.1; //default: 10%
                        if (estimateddbirr != Double.NaN)
                        {
                            irrGuess = estimateddbirr;
                            if (irrGuess <= 0.0) irrGuess = 0.5;
                        }

                        //initialize first IRR with estimated dbirr:
                        double irr = 0.0;
                        if (sumdbcf < 0)
                        { //sum of cash flows negative?
                            irr = -irrGuess;
                        }
                        else
                        { //sum of cash flows not negative
                            irr = irrGuess;
                        }

                        //iteration Newton-Raphson method:
                        final double minDistance = .0000000001; //the smaller the distance, the smaller the interpolation error
                        final double dbcftart = dbcf[0]; //business startup costs
                        final int maxIteration = 50;
                        boolean wasHi = false;
                        double cashValue = 0.0;
                        for (int i1 = 0; i1 <= maxIteration; i1++)
                        {     //for each iteration
                            //calculate cash value with current irr:
                            cashValue = dbcftart; //init value with startup costs
                            for (int j1 = 1; j1 < noOfdbcf; j1++)
                            { //for each cash flow
                                cashValue += dbcf[j1] / Math.pow(1.0 + irr, j1);
                            }//next cash flow

                            if (Math.abs(cashValue) < 0.01)
                            { //cash value is nearly zero
                                dbirr = irr;
                                break;
                            }

                            //adjust irr for next iteration:
                            if (cashValue > 0.0)
                            { //cash value > 0 => next irr > current irr
                                if (wasHi)
                                {
                                    irrGuess /= 2;
                                }

                                irr += irrGuess;

                                if (wasHi)
                                {
                                    irrGuess -= minDistance;
                                    wasHi = false;
                                }
                            }
                            else
                            { //cash value < 0 => next irr < current irr
                                irrGuess /= 2;
                                irr -= irrGuess;
                                wasHi = true;
                            }

                            if (irrGuess <= minDistance)
                            { //estimated dbirr too small to continue => end calculation
                                dbirr = irr;
                                break;
                            }
                        }//next iteration
                    }//else: noOfNegativedbcf == 0 || noOfPositivedbcf == 0
                }//else: first cash flow is 0
            }//else: dbcf unavailable
            answerirr = "IRR(%) = " + formatter.format(dbirr*100) + "%";
            result.setText((String) answerirr.toString());
            //return ir;
            }//getIRR()
        } //IRR calculation closing

2.3 Net future value - NFV Android Java source code

{

                dbnfv = dbnpv*Math.pow(1+dbrate/100,number-1);
                answernfv = "NFV = " + currency.format(dbnfv);
                result.setText(answernfv);

            } //NFV calculation closing

2.4 Net present value - XNPV Android Java source code

dbnpv = 0;
            for(i=0;i<=5;i++)
            dbnpv = dbnpv + dbcf[i]/(Math.pow((1+dbrate/100),ddiff[i]/365));

if (v==NPV)
            {
                if(ddiff[5]<0||ddiff[4]<0||ddiff[3]<0||ddiff[2]<0||ddiff[1]<0||ddiff[0]<0)
                {
                    new AlertDialog.Builder(this)
                    .setTitle("Error Message")
                    .setMessage("Please key in date in ascending order!")
                    .setNeutralButton("Close", new DialogInterface.OnClickListener() {
                    public void onClick(DialogInterface dlg, int sumthin) {
                    // do nothing – it will close on its own
                    }
                    })
                    .show();
                }
                else
                {
                    if(dbnpv<=0)
                    {
                        answernpv = "NPV = " + currency.format(dbnpv) +" , project/investment rejected.";
                        result.setText(answernpv);
                    }
                    else
                    {
                        answernpv = "NPV = " + currency.format(dbnpv) +" , project/investment accepted.";
                        result.setText(answernpv);
                    }
                }//Error message closing
            }//NPV calculation closing

2.5 Internal rate of return - XIRR Android Java source code

{
            {
               final double estimateddbirr = 0;

            Double dbirr = Double.NaN;

            if (dbcf != null && dbcf.length > 0)
            {
                //check if business startup costs is not zero:
                if (dbcf[0] != 0.0)
                {
                    final double noOfdbcf = dbcf.length;

                    double sumdbcf = 0.0;
                    //check if at least 1 positive and 1 negative cash flow exists:
                    int noOfNegativedbcf = 0;
                    int noOfPositivedbcf = 0;
                    for (int i1 = 0; i1 < noOfdbcf; i1++)
                    {
                        sumdbcf += dbcf[i1];
                        if (dbcf[i1] > 0)
                        {
                            noOfPositivedbcf++;
                        }
                        else if (dbcf[i1] < 0)
                        {
                            noOfNegativedbcf++;
                        }
                    }

                    if (noOfNegativedbcf > 0 && noOfPositivedbcf > 0)
                    { //at least 1 negative and 1 positive cash flow available?
                      //set estimated dbirr:
                        double irrGuess = 0.1; //default: 10%
                        if (estimateddbirr != Double.NaN)
                        {
                            irrGuess = estimateddbirr;
                            if (irrGuess <= 0.0) irrGuess = 0.5;
                        }

                        //initialize first IRR with estimated dbirr:
                        double irr = 0.0;
                        if (sumdbcf < 0)
                        { //sum of cash flows negative?
                            irr = -irrGuess;
                        }
                        else
                        { //sum of cash flows not negative
                            irr = irrGuess;
                        }

                        //iteration Newton-Raphson method:
                        final double minDistance = .0000000001; //the smaller the distance, the smaller the interpolation error
                        final double dbcftart = dbcf[0]; //business startup costs
                        final int maxIteration = 50;
                        boolean wasHi = false;
                        double cashValue = 0.0;
                        for (int i1 = 0; i1 <= maxIteration; i1++)
                        {     //for each iteration
                            //calculate cash value with current irr:
                            cashValue = dbcftart; //init value with startup costs
                            for (int j1 = 1; j1 < noOfdbcf; j1++)
                            { //for each cash flow
                                cashValue += dbcf[j1] / Math.pow(1.0 + irr, ddiff[j1]/365);
                            }//next cash flow

                            if (Math.abs(cashValue) < 0.01)
                            { //cash value is nearly zero
                                dbirr = irr;
                                break;
                            }

                            //adjust irr for next iteration:
                            if (cashValue > 0.0)
                            { //cash value > 0 => next irr > current irr
                                if (wasHi)
                                {
                                    irrGuess /= 2;
                                }

                                irr += irrGuess;

                                if (wasHi)
                                {
                                    irrGuess -= minDistance;
                                    wasHi = false;
                                }
                            }
                            else
                            { //cash value < 0 => next irr < current irr
                                irrGuess /= 2;
                                irr -= irrGuess;
                                wasHi = true;
                            }

                            if (irrGuess <= minDistance)
                            { //estimated dbirr too small to continue => end calculation
                                dbirr = irr;
                                break;
                            }
                        }//next iteration
                    }//else: noOfNegativedbcf == 0 || noOfPositivedbcf == 0
                }//else: first cash flow is 0
            }//else: dbcf unavailable
            answerirr = "IRR(%) = " + formatter.format(dbirr*100) + "%";
            result.setText((String) answerirr.toString());
            //return ir;
            }//getIRR()
            }//error message closing
        } //IRR calculation closing

2.6 Net future value - XNFV Android Java source code

{
                    dbnfv = dbnpv*Math.pow(1+dbrate/100,ddiff[number-1]/365);
                    answernfv = "NFV = " + currency.format(dbnfv);
                    result.setText(answernfv);
                }//Error message closing
            } //NFV calculation closing
            else if (v==IRR)
            {if(ddiff[5]<0||ddiff[4]<0||ddiff[3]<0||ddiff[2]<0||ddiff[1]<0||ddiff[0]<0)
            {
                new AlertDialog.Builder(this)
                .setTitle("Error Message")
                .setMessage("Please key in date in ascending order!")
                .setNeutralButton("Close", new DialogInterface.OnClickListener() {
                public void onClick(DialogInterface dlg, int sumthin) {
                // do nothing – it will close on its own
                }
                })
                .show();
            }

3.0 Excel Example:

3.1 Net present value - NPV

3.2 Internal rate of return - IRR

3.3 Net future value - NFV

3.4 Net present value - XNPV

3.5 Internal rate of return - XIRR

3.6 Net future value - XNFV

4.0 Practical Application

4.1 Regular Period - Highway Project

4.2 Irregular Period - Stock's return calculation

4.3 Bonus - Number of day between two dates calculation

5.0 Download - It is available at Android Market now. Search for FREENPVIRR from Application>Finance - Just in for free download. Enjoy!

Investment Planning: Asset Relocation 6 (Checking the New Component in Strengthen the Sharpe Optimization via Excel Solver)

2010-08-22T07:34:00.000-07:00

Lately, I discovered a good unit trust from an article that presented its ten years performance since 3-Jul-00 to 19-Aug-10. It is surprisingly its annualized return and standard deviation is much more better than my own portfolio. Let us name this unit trust as PSC.
Annualized Return = RATE((DATE(2010,8,19)-DATE(2000,7,3))/365.25,0,-1,4.0809) = 14.90% p.a.
So, I am introducing this unit trust to my current portfolio for the optimization.

Result:
A) Single Unit Trust
Expected Return: 2.95%, Standard Deviation: 3.21%, Sharpe Ratio: 0.8540
B) Current Optimized Portfolio
Expected Return: 3.30%, Standard Deviation: 2.35%, Sharpe Ratio: 1.3136
C) Introducing PSC into Current Optimized Portfolio
Expected Return: 3.34%, Standard Deviation: 2.16%, Sharpe Ratio: 1.4524
D) Actual Portfolio
Expected Return: 3.11%, Standard Deviation: 3.58%, Sharpe Ratio: 0.8097

Action 1:
What Happen if I relocate Genting to LPI and PIttikal to PSC?
Expected Return: 3.35%, Standard Deviation: 3.38%, Sharpe Ratio: 0.9304

Action 2:
What Happen if I relocate PIttikal to PSC?
Expected Return: 3.25%, Standard Deviation: 3.49%, Sharpe Ratio: 0.8695

Action 3:
What Happen if I relocate Genting and PIttikal to PSC?
Expected Return: 3.26%, Standard Deviation: 3.35%, Sharpe Ratio: 0.9095

Investment Exit plan - Decision Making Via XIRR and XNPV 2

2010-08-17T07:00:00.000-07:00

Extended story from Miss K (http://seannchine.blogspot.com/2010/07/investment-exit-plan-decision-making.html)

Layman calculation:
1) Holding yield (HY) and Holding period yield (HPY).
It cant be used to compare with other annualized rate (Fixed Deposit rate, Inflation, GDP, etc.). For example, HY for the share from first investment 13-Aug-06 to 16-Aug-10 is (8.47-7.6)/7.6 = 11.44%; while second investment 28-Aug-07 is (8.47-7.24)/7.24 = 16.99%. Holding Period Yield (HPY) for the individual investment are 2.68% p.a.(11.44%/4) and 5.66% (16.99%/3) respectively. In this return calculation , however, dividends given from the subsequent years are not taken into account for the HY calculation.

2) Average Return
For a layman calculation, they take average cost which is (300*7.6+1700*7.24)/(300+1700) = 7.29. Today price, said 8.47, means HY is (8.47-7.29)/7.29 = 16.19%. Holding period yield over period (HPY) = 16.19%/4 = 4.05% p.a. if compares it with FD rate 3.25% p.a., then it is not a good investment if you ignore dividend (in the case, dividend from Share G is too less, it can be ignore). Something very wrong which is using 4 years as the denominator. emm...

3) Dividend Return
If we need to take the dividend into the account, then just sum up all dividend per share (DPS), which is 0.02+0.22+0.03+0.02+0.03+0.03 = 0.35. Dividend yield is 0.35/7.29/4 = 1.2% p.a.

4) Total Return
Thus, total return is return of appreciation + return of dividend yield = 4.05+1.2% = 5.25% p.a. It is not a successful investment because it has to offset with inflation which about 4% p.a. Layman’s Real return is 5.25% - 4% = 1.25%.

In the practical way, I mean time value of money, it shall be calculated in the cash flow which varies with date given. Thus, Compounded Annualized Growth Rate (CAGR) is calculated via XIRR = 6.47% p.a. Real return = (1+5.25%)/(1+4%)-1 =1.20%.

Miss K wants to sell it because it is one of the investments that not really contribute much for her portfolio's CAGR. To increase her portfolio's CAGR, she needs to liquidate it in order to buy another better return stock. Thus, solver, once again, is used to solve for the desired CAGR return for 8%, 10% and 15%. The exit plan is suggested to liquidate the share by selling it at RM9.40 if she aims for 10% CAGR, while RM10.83 at 15% CAGR.

Overall Retirement Plan + Children Education Plan - Two Kids Plan

2010-08-03T06:27:00.000-07:00

Mr. E aged 35 years old, plans to marry now and hopes to have two kids - 1st kid right after marry and 2nd kid one year after marry. What is the retirement fund and children education fund for the overall family plan?

Assumption:
1) His wife and Mr. E are same age
2) His wife and Mr. E plans to work till 60 years old
3) His hife has monthly RM500 EPF contribution with the beginning saving of RM40,000 at the Account I and Account II. EPF annual dividend is 6.0% p.a.
4) Mr. E's asset appreciation at pre-retirement rate of 10% p.a. while post-retirement rate of 8% p.a., salary increment at 5% p.a., cost of living inflation at 3.2% p.a.,
5) Children Education fees (school fees and living cost) RM25,000 p.a., inflation at 6% p.a., education fund performance at 8%. p.a.
6) Miscellaneous expenses: RM1,500 monthly for contingency reserve

Financial Planning approaches:
1) Target Replacement Ratio: 80%
2) Retirement fund calculation - principal liquidation

V@35 denotes Mr. E's value at current age while V@60 denotes Mr.E's value at retirement age. It has a negative net worth after the consideration of offsetting liabilities with the asset. Thus,
Mr. E has to plan some action for his retirement fund and children education fund preparation.

You can replace the Mr. E figures by yours. Check and see the result.

The previous result is based on the assumption 4, 10% p.a. for the net worth appreciation CAGR. What is the desired return that Mr. E required in order to have the net worth at RM 0?
By using solver, 11% p.a. CAGR is calculated. Thus, he needs to ensure that his asset appreciation must be over 11% p.a.

However, Mr.E required a house and a car ++ till 85 years old. So, the solver is re-simulated by reserving 395k@60 years old. Thus, he needs to ensure that his asset appreciation must be over 11.28% p.a.

After the study of his portfolio CAGR return, which is 13.2% p.a. When will be his financial freedom age?

From solver, his financial freedom age is 40 years old. Thus, he has to ensure his portfolio return can generate 13.2% p.a. CAGR every year till 85 years old.

Net Worth Time Series: Single Source of Income Versus Multi Source of Income

2010-07-29T08:20:00.000-07:00

A person's net worth can be plotted in time series. Type of the time series plot can be characterized as below:

A) Robot Asimo - Staircase Type

Asimo works hard to earn steady monthly salary. Due to robot never spend any single penny (or, maybe do not know how to spend), the net worth pattern shall be look like a staircase type.

Characteristic: No human being is classified into this category. But, it shows that it is the maximum amount that one can earn from this single source of income for the rest of the life time.

B) Mr. Savi - Uptrend Saw-Type

Mr. Savi awares about the saving is important. Thus, he works hard to increase his net worth through his single source of income, most likely it is through the employment.

Characteristic: Consumption Outflow lesser than Monthly Salary Inflow

C) Ms. Balanec - Flat Saw-Type

Ms. Balanec seems to know well about the balance of spending and earning perfectly. Perhaps, she has no trust on financial planning. Spend all as per earned. She really knows how to enjoy life...young.

Characteristic: Consumption Outflow same as Monthly Salary Inflow

D) Mr. Brooke - Downtrend Saw-Type

Mr. Brooke believes in today money more worth than tomorrow, so he spends it all using his future money from credit card. How to finance his high consumption outflow? Perhaps, his father is rich...very rich.

Characteristic: Consumption Outflow higher than Monthly Salary Inflow

E) Ms. Exponen - Escalating Staircase Type

Ms. Exponen works and has developed passive incomes or another stream of active incomes.

Characteristic: Consumption Outflow is offset by multi source of income that generates exponential effect growth from the existing net worth. Due to the effect of strong inflow, she has no worry on her basic/fixed month expenses.

What is your time series pattern?

Every success starts with a hard way, always.
This is the actual time series pattern which I just started my plan. The execution is hard but it is not an impossible task.

After years, the trend changes to exponential or higher linear growth trend. It is the results of
1) increasing the capability of inflow,
2) use the increased amount of inflow to buy appreciating asset,
3) reduce liability, and
4) controlled outflow.

It is as simple as ABC, just the matter of the cashflow management. But, how many people can do that?

REIT and Shop Lot Investment in Malaysia

2010-07-27T08:42:00.000-07:00

Mr. E intends to invest in property after he receives an amount of sum assured from his deceased father's life insurance. With his own parent taught since his childhood, properties investment is the best investment vehicle that he can park his money in. Net receivable of the sum assured is RM100,000. Mr. E believes in conventional way to invest it in such way to "own" a shop physically at somewhere in the town. However, his wife receives information about REIT from his securities broker.

REIT in Malaysia is not popular, even their DY (dividend yield) recorded 7% - 8% p.a. recently. This is due to limited appreciation of the stock value itself, around 4% p.a. It is about 13 REITs listed in Bursa Malaysia.

Case Study#1: Invest RM100,000 directly to "own" a 2-storey-shop lot at KL

Assumption:

Net rental income is the gross rental income of RM7,000 offset with taxes and maintenance, said RM6,000.

Case Study#2: Invest RM100,000 in REIT

Assumption:

1) dividend per unit paid in semiannual

2) dividend per unit growth at 5% p.a.

3) Capital appreciation at 5% p.a.

Finding:

Case Study#1:

IRR = 22.62% p.a.

NPV@6.5%p.a. = RM1,114,452.25 (Accepted)

Pro/con: 1) consistence monthly rental income that can against inflation, 2) Psychologically "own" the shop, feel more secure, 3) low liquidity, 4) Higher cost (lawyer fees etc), 5) higher return, 6) high maintenance, 7) committed to loan servicing

Case Study#2:

IRR = 10.28% p.a.

NPV@3.25%p.a. = RM255,567.73 (Accepted)

Pro/con: 1) consistence semi annual dividend income that can against inflation, 2) liquidity very high, switch to other investment vehicle easier, 3) low brokerage fees, 4) lower return, 5) less maintenance, 6) No commitment

Present Value Evaluation (DDM) by using my expected return at 15% p.a.
Case Study#1:
P=D,o(1+g)/(r-g) = 12*6000(1+0.04)/(0.15-0.04) = RM680,727.30 (Assume freehold property)
Case Study#2:
P=D,o(1+g)/(r-g) = 2*2500(1+0.05)/(0.15-0.05) = RM52,500

CPA, CFA Or CFP - Pick Your Abbreviation Carefully (Diverted From Investopedia)

2010-07-21T05:53:00.000-07:00

A very good article about the best known professional designations in the financial industry, CPA, CFA and CFP. Three alphabets is short, to most Malaysian, it is really confusing. This is the article which I took from Investopedia which written by Daniel Myers. I am a CFP holder, I am considering to take my CFA in my near furture..

Whether you're searching for a good career path or just curious about the different financial credentials, this article will help guide you through three of the best known professional designations in the financial industry: Certified Public Accountant (CPA), Chartered Financial Analyst (CFA) and Certified Financial Planner (CFP). Each of these three has a core career focus, and although their abbreviations often sound interchangeable, each designation gives you something unique. Follow along to discover how much coursework and study each designation requires, what careers they typically lead to and how much you could make. (For a basic breakdown on these designations and more, check out The Alphabet Soup Of Financial Certifications.)

Certified Public Accountant (CPA)
For most people, a CPA is best known as the person who prepares tax returns. CPAs certainly do that, but they do much more as well. A CPA license is legally required in order to do particular jobs, such as public accounting (independent auditing). However, one does not require a CPA license in order to prepare tax returns. State laws govern what CPAs can and cannot do with their license. (If you're looking for a good accountant, check out Crunch The Numbers To Find The Ideal Accountant.)

Requirements: Requirements vary by state, but in general, in order to sit for the CPA exam, applicants must have a bachelor's degree with 120 semester hours. To obtain the CPA designation, applicants must pass the Uniform CPA Exam, gain relevant work experience and meet additional educational requirements. Overall, additional educational requirements usually consist of 24-30 semester hours in accounting, earned through a graduate or bachelor's degree in business. Many states also require a minimum number of one to two years accounting and/or auditing experience.

Aside from the experience requirements, a CPA license usually takes about 18 months to complete beyond the educational requirements. Many students choose to pursue a masters degree in accounting to fulfill their educational requirements.

Exam: Although classroom requirements are a major requirement, the CPA exam is a difficult task in its own right. Exams are administrated by the American Institute of Certified Public Accountants, the governing body of CPAs in the

United States

. The 14-hour computerized exam consists of four sections:

Auditing and attestation
Financial accounting and reporting
Regulation
Business environment and concepts

Careers: Many undergraduate accounting students receive job offers long before they graduate. Accounting is an in-demand field and is projected to continue to be so. According to the U.S. Bureau of Labor Statistics, employment is projected to grow 18% between 2006 and 2016; this amounts to nearly 226,000 new positions. For CPAs wishing to advance to senior-level corporate positions, two to three years of experience at a major accounting firm is crucial.

For those seeking gainful employment but not wishing to climb the corporate ladder, there are numerous positions available in every city for accountants at small accounting firms and practices. For more ambitious job seekers, lucrative CPA positions are available in hedge fund accounting and Sarbanes-Oxley-related work. The chief requirements for these positions are experience and an excellent educational background. (To learn the history of the accounting profession, check out The Rise Of The Modern-Day Bean Counter.)

Chartered Financial Analyst (CFA)The CFA reputation in the business community is world class, and CFA charterholders work in many countries around the world. The CFA program is very broad, and might be more aptly described as the equivalent of a master's degree in finance with accompanying minors in accounting, economics, statistical analysis and portfolio management. Although the CFA designation is not a legal requirement to perform work as a financial analyst (the cornerstone CFA job), it is a great way to get a foot in the door for one of the most difficult jobs to crack. (For more on the pros and cons of this demanding designation, see So You Want To Earn Your CFA? and Becoming A Financial Analyst.)

By reputation, the best way to get a finance job on Wall Street is to get a Master of Business Administration degree from one of a handful of exclusive schools including Wharton, Harvard and Stanford. The second best way is to earn the CFA charter and have good industry experience. In some instances a CFA designation is even held in higher esteem than an MBA.

Requirements: The CFA designation (granted by the CFA Institute) earns its reputation mainly due to the grueling process candidates must endure to earn the CFA charter. While the exam is very democratic and open to anyone with a bachelor's degree, the only people with a realistic chance of passing are those who are serious about the field. The three general requirements to earn a CFA charter are to pass three exams, have an undergraduate degree (in any subject) and have three years related work experience in the financial area.

Exam: To earn the CFA charter, candidates must pass a six-hour exam for each of three levels. The first exam is available twice per year (in June and December) and the next two are only available in June. The pass rates on the three tests are slightly above 50%, so if you don't pass the second or third exam, you must wait one year to take it again.

Each of the three tests has overlapping material such as ethics and financial analysis. Generally, though, the first test covers broad financial principles, the second is a very intensive exam on financial analysis and accounting, and the third exam covers portfolio management and decision making. (For more on surviving your CFA exams see, Pass Your CFA Exams The First Time.)

Careers: According to the CFA Institute, 55% of charterholders work for institutional investors as in-house analysts, 15% work for broker-dealers, and the remaining 29% work for universities and the government. While not nearly as numerous as CPA jobs, CFA-related jobs are perhaps more lucrative. CFA Institute's 2007 Member Compensation Survey reports that the median compensation for an equity portfolio manager with less than five years of experience is $495,000. With over 10 years experience, the median yearly salary went up to $555,000.

Certified Financial Planner (CFP)The Certified Financial Planner (CFP) is the only designation of the three focused on investing. It provides an extremely practical course of study for those wishing to work directly with individual investors. The focus of the CFP is to train financial advisors to create and implement financial plans for investors.

Requirements and Exam: The requirements for the CFP, as specified by the CFP Board of Standards, are a bachelor's degree in any major, three years financial planning experience, other educational requirements (see below) and an exam. The 10-hour exam covers the following: investments planning, insurance, estate planning, risk management, tax and retirement planning.

In order to take the exam, one must complete a prescribed course of study - unless exempt - in relevant financial planning areas. These six required courses take about nine months to complete and are conducted on college campuses nationally. They are:

Financial planning: process and environment
Fundamentals of insurance planning
Income taxation
Planning for retirement needs
Investments
Fundamentals of estate planning

Careers: People who benefit the most from the CFP designation are those who usually work directly with individual clients. Opportunities exist nationally for people with the CFP, but it is not necessarily a key to a high-paying job, as opposed to the CPA designation. There is no typical salary with the CFP, as it helps gain client credibility in what is essentially an entrepreneurial position. Income potential is determined by the sales performance of the financial consultant, not by a salary scale. (If you aren't sure financial planning is for you, check out the quiz in Is A Career In Financial Planning In Your Future?)

Parting ThoughtsOf the three designations, only the CPA is governed by state laws (to protect the public interest). In choosing a designation to pursue, ask yourself what kind of work you want to do, where you want to work, and if you want to work as an employee with a guaranteed salary or an entrepreneur where the sky (and the basement) is the limit. No matter which you choose, each of these three financial designations will provide ample professional opportunities for those who spend the time and energy to earn them.

Written by Daniel Myers,CFA.

Application of NPV and IRR in Renewable Energy In Malaysia

2010-07-16T06:41:00.000-07:00

Not many engineers aware about IRR/NPV method is the best way to make the decision for the project.
Luckily, I am a chemical engineer, who gained the basic knowledge of NPV and IRR via Economy Process offered by my university during my undergraduate study.

As an engineering manager, we have to make a lot of decision about the feasibility of the project. From finance point of view, expected return or finance cost for every single that the company invested shall be fruitfully produce the profit to the company.

I am not going to talk about NPV or IRR calculation here, I believe that I have discussed sufficiently in my previous chapter. Thus, the main focus here is the decision making mechanism from the calculated IRR, NPV and SPB.

Investment Exit plan - Decision Making Via XIRR and XNPV

2010-07-08T08:36:00.000-07:00

Miss K bought company G share about four years ago. Over years, she received dividend from company G semi annually. However, share price has no significant increase. Its range from RM6 to RM8. She has no idea on the calculation of the return of investment. Indecisively, she does not know how to make the move from it.

a) what is the CAGR of the share G investment? loss and gain?
b) does the invest worth to be kept if we take finance cost as the risk free rate of 4%p.a.?
c) does the invest worth to be kept if our expected rate is targeted as 15%p.a.?
d) if she wants to realize the loss/gain by taking finance cost as the risk free rate of 4%p.a. CAGR, what is the selling price for share G?

Solution:

Step 1: Tabulate the net cashflow in time series.
Step 2: Take the market value base on today price - make it liquidate by today (+ve sign)

a) what is the CAGR of the share G investment? loss and gain?

Step 3: Function XIRR is used to calculate CAGR. 1.31% gain p.a. is obtained, but it is lower than the risk free rate 4% p.a. The return 1.31% is bench-marked with 4% e.g. MGS or simply make it as 4% p.a. for fix deposit return.

b) does the invest worth to be kept if we take finance cost as the risk free rate of 4%p.a.?

Step 4: Function XNPV is used to make the investment feasibility. Positive NPV leads to make the decision to accept the project while negative NPV leads to reject the project. For the 4% p.a. benchmarking, the project has the net present value of -RM1041. Thus, we reject the project. So we should not keep the investment. We should realize the loss.

c) does the invest worth to be kept if our expected rate is targeted as 15%p.a.?

Step 5: For the 15% p.a. benchmarking, the project has the net present value of -RM3997. Thus, we reject the project. So we should not keep the investment. We should realize the loss. 15% p.a. is my favorite CAGR investment return. It is used to gauge my investment performance.

For those who had gone through the retirement and children education planning, desired return from the planning is used to benchmark the investment share G performance. For instance, Miss K desired an investment of CAGR 6% p.a., then she should use 6% to replace 15%.

d) if she wants to realize the loss/gain by taking finance cost as the risk free rate of 4%p.a. CAGR, what is the selling price for share G?
Step 6: Excel Solver is used to calculate liquidation price by fixing CAGR at 4% p.a. Thus, she should sell the company G at least higher than RM7.86 without suffer any loss.

See, as simple as ABC.

EPF Withdrawal for Unit Trust Investment Study

2010-06-30T08:03:00.000-07:00

Calculate Investment Withdrawal

You must have more than the required Basic Savings in your Account 1 to become eligible to apply for the EPF's investment withdrawal.

In addition, the amount to be withdrawn must not be less than RM1,000 and not more that 20 per cent of the amount exceeding the required Basic Savings in Account 1.

You can make further withdrawals, for investment purposes, at three-month intervals as long as you meet the eligibility criteria each time you wish to make a new investment.

Investment withdrawals can be made every 3 months after the date of the last application is approved, subject to the availability of the required balance in Account 1.

Reference: www.kwsp.gov.my

You might be approached by UT agent about the EPF scheme for the EPF approved fund investment. So, is it a right decision to withdraw it for the investment? Can it really make money?

Case Study#1: Without Withdrawal

Total: $133,576.24

Case Study#2: With the Withdrawal - Investors
How about the EPF withdrawal for investors (5.5% commission applied)?

Total: $134,435.39

Case Study#3: With the Withdrawal - Agent
How about the EPF withdrawal for Agent (assume reinvestment for the 2.75% obtained commission with 8%p.a. fund return)?

Total: $134,435.39+$946.84 =$135,382.23

Conclusion:
By assuming unit trust fund return is 8% p.a.
Case Study#1's return: 12*RATE(12,-2300,-100000,133576.24)=5.19%
Case Study#2's Return: 12*RATE(12,-2300,-100000,134435.39)=5.91%
Case Study#3's Return: 12*RATE(12,-2300,-100000,135382.23)=6.71%

Emm... Case study#2 and 3's return is not much different from Case study#1 for first year, but the effort of implementing the withdrawal is (6.71-5.19)/5.19 = 29.29% increment.

Time Value of Money in Excel (Basic)

2010-06-14T08:20:00.000-07:00

E1: What would the interest amount and principal plus interest be for a
loan of $1,500 borrowed for 90 days at an annual rate of 7.25%?
Use the 360-day mode and two decimal places.

Simple Interest: (7.25%*(90/360))*1500 = 27.19
Simple interest + Principal: (1+7.25%*(90/360))*1500 = 1527.19.

E2: Calculate the interest rate required to increase a principal of $10,000
to $12,000 in three years, when compounding is performed semiannually.

Compound Interest: (period of compounding per year)*RATE(Total compounding time,PMT,PV,FV,TYPE)

Annual Rate: 2*RATE(2*3,0,-10000,12000,0) = 6.17% p.a.

E3: Calculate the interest rate required to have a $2,500 balance in an
installment savings account in two years when $100 is deposited each
month and interest is compounded semiannually.

Compound Interest: EFFECT((period of compounding per year)*RATE(Total compounding time,PMT,PV,FV,TYPE),compounding per year)

Annual Rate: EFFECT(12*RATE(2*12,-100,0,2500,0),2) = 4.28% p.a.

E4: Calculate the interest rate required to repay a $2,300 balance on a loan
in two years paying back $100 per month, when interest is compounded
monthly.

Annual Rate: 12*RATE(2*12,-100,2300,0,0) = 4.11% p.a.

E5: Calculate the interest rate required to repay a $2,500 balance on a loan
in two years (24 installments) paying back $100 per month and a final
$200 installment, when interest is compounded monthly.

Annual Rate: 12*RATE(2*12,-100,2500,-200,0) = 3.54% p.a.

E6: Calculate the future value after 7.6 years for a principal of $500 and an
interest rate of 6%, compounded annually.

Future Value: FV(6%,7.6,0,-500,0) = $778.56.

E7: Calculate the principal required at 5.5%, compounded monthly, to
produce a total of $20,000 in a year.

Present Value: PV(5.5%/12,12,0,20000,0) = -$18,932.08

E8: Calculate the interest required, compounded monthly, to produce a
total of $10,000 in 10 years on an initial investment of $6,000.

Annual Rate: 12*RATE(10*12,0,-6000,10000,0) = 5.12% p.a.

E9: Calculate the amount of time required to increase an initial investment
of $5,000 to a total of $10,000 at an annual rate of 4%, compounded
monthly.

NPER: NPER(4%/12,0,-5000,10000,0)/12 = 17.35 years

E10: Calculate (to two decimal places) the principal plus interest for $250
monthly installments for five years at 6% annual interest, compounded
monthly.
Calculate amounts for when installments are made at the beginning of
each month and at the end of each month.

Future Value (End Mode): FV(6%/12,5*12,-250,0,0) = $17,442.51
Future Value (Begin Mode): FV(6%/12,5*12,-250,0,1) = $17,529.72

E11: Calculate the amount required for each installment to accumulate a
total of $10,000 in 5 years at an annual interest rate of 6%,
compounded semiannually.

PMT: PMT(EFFECT(6%,2)/12,5*12,0,10000,0) = -$143

E12: Calculate the number of monthly $84 installments required to
accumulate a total of $6,000 at an annual interest rate of 6%,
compounded annually.

NPER: NPER(6%/12,-84,0,6000,0) = 62 monthly installment

E13: Calculate the principal plus interest after one year for an installment
savings account with an interest rate of 4.5%, compounded monthly,
opened with an initial deposit of $1,000, with $500 installments added
each month.

Future Value: FV(4.5%/12,12,-500,-1000,0) = $7,171.25

E14: Calculate how much can be borrowed on a 15-year loan at a 7.5%
annual interest rate, compounded monthly, if a payment of $450 per
month can be made.

Present Value: PV(7.5%/12,15*12,-450,0,0) = $48,543.04

E15: Calculate the size of the monthly installment for a 25-year $300,000
home loan made at 6.2%, compounded semiannually.

PMT: PMT(EFFECT(6.2%,2)/12,25*12,300000,0,0) = -$1,987.57

E16: Calculate the number of installments it will take to repay a $60,000
loan borrowed at 5.5%, compounded monthly, with monthly
installments of $840.

Period: NPER(EFFECT(5.5%,12)/12,-840,60000,0,0) = 87 months

E17: Calculate (to two decimal places) the effective interest rate
compounded monthly, on a 25-year $65,000 loan repaid with $460
monthly installments.

Effective Interest: 12*RATE(25*12,-460,65000,0,0) = 7.01%

E18: An investment of $86,000 in machinery projects the annual revenues
shown in the table below (all revenues realized at the end of the fiscal
year). What is the net present value of this investment if the useful
service life of the machine is six years, the resale value after six years
is $14,000, and the capital cost is 11%?

Year Revenues
1 –5,000
2 42,000
3 31,000
4 24,000
5 23,000
6 12,000 + 14,000

Method 1:
-86000
-5000
42000
31000
24000
23000
26000
$9,610.16 =NPV(11%,-5000,42000,31000,24000,23000,26000)+(-86000)

Due to Excel’s NPV doesn’t really calculate NPV, so Method 2: XNPV is always to be used.

1-Jan-10 -86000
1-Jan-11 -5000
1-Jan-12 42000
1-Jan-13 31000
1-Jan-14 24000
1-Jan-15 23000
1-Jan-16 26000
$9,591.28 =XNPV(11%,C11:C17,B11:B17)

E19: Calculate the monthly installment due on a $140,000 15-year home
mortgage at an annual rate of 6.5%, compounded semiannually.
Also calculate PRN and INT for the second year (24th installment),
BAL for installment 49, and ΣINT, ΣPRN for installments 24 through 49.

PMT: PMT(EFFECT(6.5%,2)/12,15*12,-140000,0) = $1,227.69
INT,24: ISPMT(EFFECT(6.5%,2)/12,24,12*15,-140000)= $667.90
PRN,24: 1227.69-667.90 = $559.79
BAL,49: FV(EFFECT(6.5%,2)/12,49,1227.69,-140000) = $114,374.60
ΣINT,24-49: CUMIPMT(EFFECT(6.5%,2)/12,15*12,140000,24,49,0) = -$17468.01
ΣPRN,24-49: CUMPRINC(EFFECT(6.5%,2)/12,15*12,140000,24,49,0) = -$14452.04

E20: Calculate (to two decimal places) the effective interest rate for an
account paying an interest rate of 12%, compounded quarterly.
Effective Rate: EFFECT(12%,4) = 12.55%

E21: Calculate the annual percentage rate for an account paying an
effective interest rate of 12.55%, compounded quarterly.
Annual Percentage Rate: NOMINAL(12.55%,4) = 11.99%

Amortization in Excel 2: Smart Loan Malaysia Comparison

2010-06-10T07:17:00.000-07:00

Recap: please read Amortization in Excel: Do You Know Your Loan Well?

Mortgage Refinancing Parameter: completed, Selangor, RM100,000 loan, conventional and Islamic loan, floating interest, 10-year-tenure, no flexi loan. BLR=6.05%p.a.

Loan Comparison Platform: http://www.smartloans.my

Let's compare the cheapest and most expansive package.

Example 1: Hong Leong Flexi Package (BLR-1.9%) - Cheapest
1) Interest Rate = RATE(nper,pmt,pv,fv,type)

= 12*Rate(12*10,-1020,100000,0,0) = 4.16% p.a.

2) Total Interest Paid for 10 years loan = CUMIPMT(rate,nper,pv,start_period,end_period,type)
= CUMIPMT(0.0416/12,12*10,100000,1,12*10,0) = -22,408.80

3) Ratio = (interest paid + principle)/principle
= (22408.80+100000)/100000 = 1.22.

Example 2: Standard Chartered - Standard Term Loan - Most Expansive
How to calculate the Standard Chartered - Standard Term Loan?
Uneven cashflow is determined in the study via XIRR.
First 1 year: RM1,022, Next 4 years: RM1,025, thereafter RM1,205. Bank always confuses his customer, and it leads to difficult way to compare with others.

1) IRR = 5.99%p.a.

2) Total Interest Paid for 10 years loan = CUMIPMT(rate,nper,pv,star

t_period,end_period,type)
= CUMIPMT(0.0599/12,12*10,100000,1,12*10,0) = -33164.43.

3) Ratio = (interest paid + principle)/principle
= (33164.43+100000)/100000 = 1.33.

Dollar Cost Averaging and Single Lump Sum Comparison

2010-06-07T08:16:00.000-07:00

Scenario 1: Mr. J did invest single lump sum RM31,000 into an UT on 13-Dec-07. Today's value of this UT suffers loss of 9.81% for holding period (13-Dec-07 till 4-Jun-10), which its value is RM26,421.16, CAGR = -6.27% p.a.

Scenario 2: Mr. K started his first investment for the same UT for RM1,000 on 13-Dec-07. And, he practiced his monthly investment amounted RM1,000 into the UT on the 1st day of the month till 1-Jun-10. Today's value of this UT generates gain of 3.82% for holding period (13-Dec-07 till 4-Jun-10), which its value is RM32,183.39, CAGR = 3.03% p.a.

Rule: practicing in dollar cost averaging only be effective on the best quality share/UT.

Discussion:
No body can time the market. So, dollar cost average works perfectly during the price going down because it buys more during downtime. Thus, cultivate the discipline of averaging the share/unit that u bought. It is proven a power statistical way to average the risk of buying high or selling low. The best example, I will quote EPF. Discipline, force saving, only invest no withdraw and well-managed fund are the characteristic of an effective saving. Doubtful? Then you many compare your saving by your own effort with saving by EPF. The EPF's results tell you the result.

In reality, people are hard to be educated to have the awareness of the dollar cost averaging. They only buy it during price is on the way up and sell it during price in on the way down. It is mainly because of the lack of education in investment.

To make it effective, cutting cost for the transaction is very important for the investment. You may buy share (due to very minimal cost to have the transaction done), UT (with agent's price or staff price) or buy it directly from FundSuperMart (cheaper service charge). Most importantly, Malaysia doesnt impose capital gain tax on the share capital appreciation, but income tax on dividend and distribution from share/UT. Let us study scenario 3.

Scenario 3: Miss L is an agent who attaches to one of the UTC. She pays (1-5.5%+2.75%=97.25% simplified) for each buying transaction. Today's value of this UT generates gain of 6.84% for holding period (13-Dec-07 till 4-Jun-10), which its value is RM32,183.39, CAGR = 5.38% p.a.

If I got time, next day we discuss VCA - value cost averaging.