Email:      Pass:    Pass?

Excel Finance Trick #2: Simple & Compound Interest

Video | Similar Helpful Excel Resources

See how to use the FV function. See the math formula for calculating Future Value and for calculating the effective interest rate. Also see long hand how compound interest is calculated.

See that Excel only sees 15 significant digits in Excel. Learn that Excel 2007 has 100 Undo, whereas Excel 2003 only had 16.

In This Series learn 17 amazing Finance Tricks. Learn about the PMT, PV, FV, NPER, RATE, SLN, DB, EFFECT, NOMINAL, NPV, XNPV, and the CUMIPMT functions that can make your financing tasks much easier in Excel. See how to use the PMT function in the standard way, but also see how to use it while incorporating a Balloon payment or a delayed payment. Lean how to translate a Nominal interest rate into an Effective Interest rate. Learn how to calculate how long it takes to pay off a credit card balance. Lean how to calculate the Effect Rate on a Payday loan. And many more financing Tricks!!

The Excel Finance Tricks 1-17 will show an assortment of Excel Financing Tricks!


Got a Question? Ask it Here in the Forum.

Similar Helpful Excel Resources

I want to know the function to calculate the SI and the CI in Excel. I know manually we calculate the Simple Interest = P*R*T and compound Interest = P(1+i)^n . When I calculate the SI using inbuilt function of Excel i.e. PMT as shown in the following link

It given right answer. But if on same data i apply P*R*T it gives something different. Also I saw following site

it uses FV function. Kindly advice how to calculate the Si and CI using inbuilt functions. I just want to calculate it when there is no change in the rate of interest in the subsequent years and it will remain same.


Harbinder Singh

Does Excel contain a simple interest loan calculation function built in? Or do I need to specify how exactly simple interest calculates on a per diem basis? What I am trying to do is calculate finance charge based on daily interest such as a standard auto loan contract. I am supplying the interest rate, term, and days to first payment - which in itself contains all of the necessary data for calculation (assuming excel has the loan calculation built in), however simple interest is based daily, so the calculation needs to understand days in each month, and totals days across a long period of time, and I seem to remember excel having basic loan calculation features built in, however general excel help only seems to have basic functions. What would be the best way to approach this?

Hi, I have one large file and I need to compute the final value of investing a particular amount based on the returns given by the stock market. I'm trying to figure out a way that I can compute the answer without going into cell after cell with the same kind of formula. The formula that I'm looking for is like the formula for a compound interest. I.e. Final value = P(1+r)^n. However, in my case, r is always changing and hence P is also changing as well. What I'm currently doing is typing the same kind of formula into each cell. For example to find the year-end value I input: 1000(1.05) then the next year, the year-end value is this: (1000)(1.05)(1.10), and the next may be (1000)(1.05)(0.96)...and so on....does anyone know of a simpler way to do this?

I'm trying to find an elegant way of calculating compound interest on outstanding debts; one big problem is that clients occasionally make ad-hoc payments so I need to maintain a running total, using a changing bank base lending rate. I have taken quite a long look round and it seems this question has not come up before, I hope!

How can I calculate the amount I would receive in compound interest on a
fixed amount at a fixed interest rate for a fixed peirod. Should be easy !

I need to create a formula that calculates compound Interest.

I deposit $10 000 in the bank.
I earn 4% interest annually, paid monthly
If I leave the money for 5 years, How much interest would I earn?


How to calculate past due fees on balances over 30 days past due?

The balances are past due for 1 to 40 or more months. The monthly payment is 1500.00 and a 10% past due fee is to be added to the balance each month plus the previous months interest. Is there a formula to show interest each month and the new balance by month?