Investment income percentage in excel

Investment income percentage is a measure of how much income an investment generates relative to its initial cost. It can be used to compare the performance of different investments or to evaluate the profitability of a single investment over time.

To calculate investment income percentage in Excel, you can use the following formula:

= (Income – Cost) / Cost

This formula subtracts the initial cost of the investment from the income it produces, and then divides the result by the cost. The output is a decimal number that can be formatted as a percentage in Excel.

For example, suppose you invested $10,000 in a bond that pays $500 in interest every year. To find the investment income percentage, you would enter the following formula in Excel:

= ($500 – $10,000) / $10,000

The result is -0.95, which means that the investment income percentage is -95%. This indicates that the investment is losing money, as the income is much lower than the cost.

To format the result as a percentage, you can select the cell and click on the Percentage button in the Number group on the Home tab. Alternatively, you can press Ctrl + Shift + % on your keyboard. This will change the display of the cell to -95.00%.

To make the calculation more dynamic, you can use cell references instead of hard-coded values. For example, you can enter the cost of the investment in cell A2, the income in cell B2, and the formula in cell C2. This way, you can easily change the input values and see the updated result.

Here is an example of how the Excel spreadsheet might look like:

To illustrate the concept further, let’s create a scenario with real numbers. Suppose you have $50,000 to invest and you are considering two options: a stock that pays $2,000 in dividends per year and has a current price of $100 per share, and a bond that pays $1,500 in interest per year and has a face value of $1,000. You want to compare the investment income percentage of each option.

To do this, you need to calculate the cost and the income of each option. For the stock, the cost is the number of shares you can buy with $50,000, multiplied by the price per share. The income is the number of shares you own, multiplied by the dividend per share. For the bond, the cost is the number of bonds you can buy with $50,000, divided by the face value of the bond. The income is the number of bonds you own, multiplied by the interest per bond.

Using Excel, you can enter the following formulas:

The ROUNDDOWN function is used to round down the number of shares or bonds to the nearest integer, as you cannot buy fractional units. The result is as follows:

The investment income percentage of the stock option is 2%, while the investment income percentage of the bond option is 1.5%. This means that the stock option generates more income relative to its cost than the bond option.

However, investment income percentage is not the only factor to consider when evaluating an investment. You should also take into account the risk, volatility, growth potential, tax implications, and other aspects of the investment. For example, the stock option might have a higher chance of losing value or fluctuating in price than the bond option, which might affect your overall return.

Another approach to calculate investment income percentage is to use the RATE function in Excel. This function returns the interest rate per period of an annuity, which is a series of fixed payments made at regular intervals. An investment can be viewed as an annuity, where the initial cost is the present value, the income is the payment, and the number of periods is the duration of the investment.

The syntax of the RATE function is:

= RATE (nper, pmt, pv, [fv], [type], [guess])

where:

• nper is the total number of payment periods.
• pmt is the payment made each period.
• pv is the present value of the annuity.
• fv is the future value of the annuity, which is optional and defaults to 0.
• type is the timing of the payment, which is optional and can be either 0 (end of period) or 1 (beginning of period). It defaults to 0.
• guess is an initial estimate of the interest rate, which is optional and defaults to 0.1.

Using the same scenario as before, you can enter the following formulas to calculate the investment income percentage using the RATE function:

Note that the present value is entered as a negative number, as it represents an outflow of cash. The result is as follows:

The investment income percentage is the same as before, as the RATE function returns the annual interest rate of the annuity. However, this approach might be more convenient if you have multiple periods or different payment timings.