The video demonstrates the way of calculating the interest rates for payday loans using MS Excel. The person presenting the video has got his MS Excel sheet with all the formulas and data ready. He first starts with the nominal & effect & future value tab. He gives us an example where you give the lenders a check of $250 that has a date 25 days in future and get $200 loan today. Now he teaches us hot to calculate APR and EAR. HE has got the data ready there, the period of the loan which is 25 days, the check amount that will be cashed in the future which is $250 the future value and the loan amount you get today which is $200 the present value. Next he calculates the 25 day interest rate by dividing the future value by the present value and subtracting it by 1. The interest rate happens to be 25% which is terrible. Next he tells you to find out the total number of periods in a year. It is calculated by dividing 365 by 25 and the resulting value is 14.60. The next step he says is to calculate the APR or the nominal rate which is the period rate times the number of periods in a year. The APR thus calculated is 365%. The final rate to be calculated is the EAR, the Effective Annual Rate for which the formula used is the value of the period rate subtracted from 1 and the exponent of this value with the number of periods in a year and finally subtracting the value by 1. The EAR thus calculated is found to be 2499.48%. He then shows you the built-in function for EAR calculation which is equal to the EFFECT function of the nominal value 365 and the number of periods 14.60. The resulting value is 2462.32%. The difference in the EAR value found using the math formula and the function in MS Excel is because the function truncates the number of periods value which means it takes 14 as the number of periods and not 14.60, thus resulting in a lesser EAR value. By following this formula you can now calculate the accurate EAR from now on.
- Hot
- Latest
-
How To: Find a P-Value with Excel
-
How To: Create a Basic Attendance Sheet in Excel
-
How To: Create a pass-fail grade formula in Microsoft Excel
-
How To: Chart a categorical frequency distribution in MS Excel
-
How To: Create an Excel spreadsheet to calculate your GPA
-
How To: Completely hide the Ribbon menu in Excel
-
How To: Cross tabulate categorical data with formulas in Excel
-
How To: Use Microsoft Publisher to add a shadow to text
-
How To: Extract a number from a text string by Excel formula
-
How To: Generate random numbers (with decimals) in Excel
-
How To: Enter formulas in Excel with a macro
-
How To: Create a Tangent Line with Excel
-
How To: Use conditional formatting for a student grading report in Microsoft Excel
-
How To: Calculate probability in an Excel pivot table
-
How To: Calculate monthly retirement income in Microsoft Excel
-
How To: Find critical values with T functions in MS Excel
-
How To: Use the IF function with letter grades in Excel
-
How To: Compare two lists and extract the new values in Excel
-
How To: Create an array formula for Bayes' theorem in Excel
-
How To: Embed a video in a slide in Microsoft PowerPoint Mac 2011
-
How To: Find a P-Value with Excel
-
How To: Create a Basic Attendance Sheet in Excel
-
How To: Create a pass-fail grade formula in Microsoft Excel
-
How To: Chart a categorical frequency distribution in MS Excel
-
How To: Create an Excel spreadsheet to calculate your GPA
-
How To: Completely hide the Ribbon menu in Excel
-
How To: Cross tabulate categorical data with formulas in Excel
-
How To: Use Microsoft Publisher to add a shadow to text
-
How To: Extract a number from a text string by Excel formula
-
How To: Generate random numbers (with decimals) in Excel
-
How To: Enter formulas in Excel with a macro
-
How To: Create a Tangent Line with Excel
-
How To: Use conditional formatting for a student grading report in Microsoft Excel
-
How To: Calculate probability in an Excel pivot table
-
How To: Calculate monthly retirement income in Microsoft Excel
-
How To: Find critical values with T functions in MS Excel
-
How To: Use the IF function with letter grades in Excel
-
How To: Compare two lists and extract the new values in Excel
-
How To: Create an array formula for Bayes' theorem in Excel
-
How To: Embed a video in a slide in Microsoft PowerPoint Mac 2011
Be the First to Comment
Share Your Thoughts