Given the inputs in yellow cells, check boxes and option buttons, this sheet uses an amortization schedule to calculate APR (or APY) for a fixed rate mortgage. Both Loan Balance and Interest Rate need no explanations. Truth in Lending Fees are additional dollar costs to the borrower to acquire the loan. Points represent a percent of the loan balance that will be charged, in addition to the Truth in Lending Fees. Amortization Years are the number of years that will be used to calculate the loan payment, not necessarily the maturity. Balloon Payment (Years) will be zero, unless the loan comes due before the Amortization Years. There are three other inputs. A drop down box for payment frequency (which will almost always be Monthly, a check box if the loan is interest only, and option buttons in case the APY is needed.
In addition, the following information is calculated:
Download workbook “APR-Fixed-Rate-Mortgage” from:
Downloads Written in Excel 2013