Excel makes it easy to calculate effective mortgage, car loan, and small business loan interest rates from the nominal rates often quoted by lending institutions.
Effective vs. Nominal Interest Rates
Lending institutions often quote nominal interest rates because they can make the cost of a loan appear lower than if the actual cost of the loan were quoted. This is because there are normally multiple payments and interest calculations made in a year. ..
However, if you miss a payment, the interest rate jumps to 18% - even if you have made all of your other payments on time. This is called a “default interest rate.” ..
The effective interest rate on a loan is higher because it compounded against interest in the past month.
As it turns out, a 12% APR interest loan has an effective interest rate of about 12.68%. This means that, over the course of a year, you would earn an extra $12.68 on your loan - which is pretty significant! ..
A one-year loan with a 12% interest rate is much cheaper than a long-term loan with an interest rate of 12.68%. ..
To calculate an effective interest rate (APY) from a nominal interest rate (APR), use the EFFECT formula. The EFFECT formula calculates the effective interest rate for a given period of time and amount of money. The formula is as follows: EFFECT = [(NominalInterestRate - APR)] / 100 The EFFECT formula can be used to calculate an effective interest rate for any type of loan, including student loans, car loans, and home loans.
Use Excel’s EFFECT Formula
The effective interest rate (APY) from a 12% nominal rate (APR) loan that has monthly compounding is 0.12%.
Notice that we have the APR in cell B1 and the number of payment periods in cells B2.
To figure out the effective interest rate for a specific category of loans, click on the cell at B3, click on the Insert Function button, and choose Financial from the drop down menu labeled Or Select a Category.
EFFECT Click on the EFFECT button to change the effect of your photo.
This will open up the Functions Argument window. In the Nominal_rate box, type in B1 and in the Npery box, type in B2. Then, click the OK button.
Excel places the figure 0.1268 in the B3 cell. If you prefer, you can change the format of the B3 cell to a percentage. ..
The effective interest rate (APY) for a loan with a nominal interest rate of 6% is 6.17%.
To figure out an effective interest rate, you can use the EFFECT function in Excel. This function takes into account the nominal rate and the number of compounding periods in a year. The EFFECT function can help you find an effective interest rate that is appropriate for your situation.
