Have you checked how many different ready made formulas can be found under formulas menu ?

For example, these formulas are under logical section and there is still side bar that some functions are not visible.

I checked duration as I have studied some finance.

Duration means that if you invest in bonds, after how many years you will get back the money you invested in bonds.

You invest 100 € in bonds and you will get 3 % coupon interest annually, means 3 € a year. At the end of maturity 10 years, you will get both invested capital and coupon back 103 €. Market yield is 8 %.

The formula in F2 is =1/(1+$B$6)^D2, meaning 3/(1+1,08)^1. This means the cash flow is discounted with 8 % interest.

G2 has formula =E2*F2. That is the cash flow in present value. H2 has sentence =G2/SUM($G$2:$G$11). H2 calculates percentage of cash flows in present value per year. I2 is =D2*H2. This is year multiplied by percentage of cash flows in present value per year.

Finally, the values in (I2:11) are summed. That value is duration. The investor would get the money invested back in close to 8,5 years. If coupon interest is increased, the duration is decreased, as the investor gets the money back bit faster.

Let’s see how the standard DURATION function under financial functions works.

The arguments for the DURATION function are:

Settlement: start date of the bond.

Maturity: end date of the bond.

Coupon: annual interest rate for the bond.

Yield: discount percentage, the cash flow is discounted to present.

Frequency: how many times a year the coupon interest is paid.

Basis: how many days are in a year. This parameter does not affect much the formula, according to my testings.

The DURATION function returns the same value as value calculated manually. Of course, ready made function is faster and easier. Still, it is useful to understand how function is calculating the values.

DURATION is just one example out of hundreds of formulas in Excel. You might find formulas suitable for your requirements and then you don’t have to enter all the values manually.