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 […]
Month: October 2014
PV and FV of Periodic Cash Flows
I thought I had already posted this spreadsheet, but it looks like I missed it. The sheet uses both formulas and Excel functions to calculate the present value, future value and also shows how to calculate the APR and APY on a cash flow, give either the present or future value. This sheet is very […]
Modified & Effective Duration, Plus Convexity of a Loan
The spreadsheet has been changed for this post. Please go to: Effective Duration, Convexity, and Convexity Adjustment For Loans My last post showed the formula to calculate a Macaulay Duration on an amortizing loan. The definition of a Macaulay Duration I used was “an effort to more accurately measure the term of an option-free fixed […]
Macaulay Duration of an Amortizing Loan
The term “duration” is often used interchangeably by market participants to denote either a volatility measurement, a weighted term to maturity, or a portfolio risk measurement. In practice there is more than one definition of duration. The following describes the most basic duration along with it’s calculations. In a future posts we will discuss Modified Duration, Effective Duration, […]