Download Compound Interest: Quarterly, Monthly, and Daily Calculations and more Summaries Business Accounting in PDF only on Docsity!
Compounding Quarterly, Monthly, and Daily
So far, you have been compounding interest annually, which means the interest is added once per year. However, you will want to add the interest quarterly, monthly, or daily in some cases. Excel will allow you to make these calculations by adjusting the interest rate and the number of periods to be compounded. Remember that all interest rates provided in the problems are annual rates. You must adjust them to fit other compounding periods. The adjusted rate is called the periodic rate. To adjust the periodic rate in Excel, open the FV calculation box and change a 10% annual rate to quarterly, monthly, or daily as follows:
- Quarterly Rate: .10/4 Changing the rate to 2.5% or.
- Monthly Rate: .10/12 Changing the rate to .83% or.
- Daily Rate: .10/365 Changing the rate to .0274% or. Change ten years of compounding to quarterly, monthly, or daily as follows:
- Quarerly Nper: 10*4 Changing the compounding periods to 40
- Monthly Nper: 10*12 Changing the compounding periods to 120
- Daily Nper: 10365 Changing the compounding periods to 3, If you assume you put $50 into savings and you are comparing savings accounts where the 10% annual interest rate is compounding quarterly, monthly, or daily. You can compare the amount of interest you will earn using Excel as follows: Quarterly Monthly Daily Rate: .1/4 or .025 Rate: .1/12 or .00833 Rate: .1/365or. Nper: 104 or 40 Nper: 1012 or 120 Nper: 10365 or 3650 Pmt: 0 Pmt: 0 Pmt: 0 Pv: - 50 Pv: - 50 Pv: - 50 Future Value = $134.25 Future Value = $135.35 Future Value = $135. The more frequently interest is added to your savings and compounded, the more interest you will earn. The above illustration involves a small amount of savings. The more the savings and the more often you add to your savings the more difference it will make when the interest in added and compounded more frequently. The following example illustrates saving $100 per month for ten years at 10% interest rate compounded monthly versus annually. Annually Monthly Rate: .1 or 10% Rate: .1/12 or. Nper: 10 Nper: 10* Pmt: - 1200 Pmt: - 100 Pv: 0 Pv: 0 Future Value = $19,124.91 Future Value = $20,484. Section 3.
Savings Plan Formula for a lump sum ๐จ = ๐ท ๐ +
๐๐ Savings Plan Formula with payment ๐จ = ๐ท๐ด๐ป
๐๐ โ ๐ ๐ ๐ Thus we have the monster formula for a Savings Plan that begins with a balance and then is added to by a payment: ๐จ = ๐ท ๐ +
๐๐
๐๐ โ ๐ ๐ ๐ Spreadsheets normally have this formula built into their functions. It is known as Future Value (FV), so you wonโt need to use this one if you learn the spreadsheet well. Loan Payment Formula ๐ท๐ด๐ป = ๐ท
!๐๐ Spreadsheets also normally have this formula built into their functions. It is known as Payment (PMT). Final note using a spreadsheet: The formulas are built so that money going out from you is negative and money coming in to you is positive. When you are entering Savings into the spreadsheet, the payment and Principal (Present Value) will be negative. However, for a loan, the payment will be negative but the Principal (Present Value) will be positive, because it represents money coming to you. A = Final Amount PMT = monthly payment P = Principal amount (beginning balance) r = annual interest rate n = number of compounding per year Y = number of years So, ๐ซ ๐ง = periodic interest rate (rate used in spreadsheet) nY = number of periods (nper) Section 3.
TIP: You can have Excel calculate this for you by entering the Pmt function to calculate the monthly payment and then, on the formula bar at the top of the Excel sheet, multiply by 48 payments and subtract the $15,000 you borrowed. The formula will be as follows: =PMT(0.06/12,412,-15000,0)48- 15000 You can also double click on the cell with the Pmt calculation in it and the formula will appear in the cell. Now you can multiply by 48 payments and subtract 15000 and enter this formula in the cell. The cell will have the answer and the formula will be in the formula bar. Interest Collected on Your Savings The interest you will earn on your savings of $350.00 per month earning 6% annual interest for 39 months (the number of months we calculated above would be required to accumulate $15,000 in savings) is calculated using the FV function in Excel as follows: Rate: .06/ Nper: 39 Pmt: - 350 Pv: 0 FV = $15,030. Amount Deposited in Savings = $35039 (deposits) = $13,650. Interest Earned on Savings = $15,030.44-$13,650.00 = $1,380. Again, you can double click on the cell containing the FV calculation and subtract 35039 and enter this formula giving you the amount of interest earned. You can make the same adjustment to the formula in the formula bar. The resulting formula is as follows: =FV(0.06/12,39,-350)-350* Total Savings From Saving Versus Borrowing Here is how you benefited by saving and paying cash for the car rather than borrowing the money to buy the car: Interest Earned $1,380. Interest Not Paid $1,909. Financial Advantage $3,289. You are wealthier by $3,289.66 because you collected interest rather than paying interest. This practice will make a major difference in your financial well being throughout your life. If you put the money you save by paying cash for major purchases to work for you by investing it for your retirement you will add greatly to your independent wealth. You Section 3.
can estimate that using the FV function in Excel as follows assuming a 6% return on your investment for 30 years: Rate:. Nper 30 Pv: - 3289. FV = $18,894. This addition to your wealth along with the other additions resulting from saving rather than borrowing will make a major impact on your ultimate wealth. TIP: In all of the Excel functions you will be using, you only need three entries or factors to calculate the fourth factor you are after. Notice that there are only three entries in each of the above Excel functions. You can leave blank any factor not needed and Excel will assume it is zero. Section 3.
Using a spreadsheet and the Future Value (FV) formula, fill out the table for a savings account. Put your results in a spreadsheet called โSavings and Loan Practice.โ
26. Simple n = 1 27. Quarterly n = 4 28. Monthly n = 12 29. Daily n = 365 P = 300 r = 8% Y = 15 A =
P = 300
r = 8% Y = 15 A =
P = 300
r = 8% Y = 15 A =
P = 300
r = 8% Y = 15 A = Using your calculator, find the monthly (n = 12) payment for the following loans. 30. P = 300 r = 8% Y = 2 PMT =
P = 3000
r = 9% Y = 5 PMT =
P = 1500
r = 15% Y = 12 PMT =
P = 2 3,
r = 8% Y = 30 PMT = Using a spreadsheet and the Payment (PMT) formula, find the monthly (n = 12) payment for the following loans. Put your results in a spreadsheet called โSavings and Loan Practice.โ 34. P = 300 r = 8% Y = 2 PMT =
P = 3000
r = 9% Y = 5 PMT =
P = 1500
r = 15% Y = 12 PMT =
P = 2 3,
r = 8% Y = 30 PMT = Using a spreadsheet and the Payment (PMT) formula, find the monthly (n = 12) payment for the following loans. Put your results in a spreadsheet called โSavings and Loan Practice.โ 38. P = 500 r = 4% Y = 2 PMT =
P = 4800
r = 9% Y = 5 PMT =
P = 2500
r = 15% Y = 12 PMT =
P = 2 3,
r = 8% Y = 20 PMT =
42. Ensure that every member of the group is able to put in the formulas and use the spreadsheet to do the calculations. (You will NOT submit a copy of your spreadsheet to answer this question. This question is complete and correct when every student is able to put in the formulas and use the spreadsheet to do the calculations.) Assignment 3.3b
Answers:
1. 216m
12
2. x
54
! !!!^
4. t
(^13) m 8
! !"
6. 12x
9
! !!^
8.^1 35. 62.
!" !"!!"^
!" !"!!"^
!"!!" !"
12. 20x
(^12) โ 35x (^11) + 25x 3
13. Negative exponents mean
division
14. 634.43^ 41. 192.
15. 656.21^ 42. Complete only when everyone understands
and can enter the formulas on their own.
Assignment 3.3b
