When person A borrows money from person B, person B usually expects to get paid back the money that was borrowed (the principal amount, P), plus a fee for the use of their money (the interest, I). From the point of view of the lender, B, they are making an investment, with a present value (PV = P) in the expectation of getting back a future value (FV = P + I).
Usually investors do not wish to wait until the end of the term of the loan to get paid. They expect periodic payments of at least the interest (in the case of bonds), and perhaps part of the principal as well (in the case of mortgages). Here are some of the relevant formulas:
For a bond, the principal is call the face amount, FaceAmount and the amount of interest to be paid back annually is called the coupon Coupon, given as a percentage. The interest period (IntPeriod) is given in months (usually six months). Then the periodic payment (PerPayment) will be:
PerPayment = FaceAmount * (Coupon/100)*(IntPeriod/12)
A Bond has an issue date (IssueDate) and usually has a call date (CallDate) and a maturity date (MatDate). If the VBA function DateDiff("m",date1,date2) returns the difference in months between date1 and date2 (with the earlier date first), then the total interest to maturity (IntMat) and call (IntCall) are approximately given by
IntMat = FaceAmount * (Coupon/100) *
DateDiff("m",IssueDate,MatDate))/12
IntCall = FaceAmount * (Coupon/100) * DateDiff("m",IssueDate,CallDate)/12
With a one day fudge needed on the Maturity or Call date to complete the final month and a more precise calaculation to the day, rather than the month needed to conform to common trading conventions
To complicate things more, bonds are actually traded and revalued against current interest rates and may sell above or below the face amount.
For a mortage, the borrower is expected to pay some amount of interest and some amount of principal each month. There are many possible mortage schemes, but the calssic fixed rate mortage is based in a single annual interest rate (IntRate) as a percentage, a monthly interest period (IntPeriod=1) and a fixed total payment (Payment) set so that the entire loan will be paid off in a fixed term of years (Term). call the original loan amount, LoanAmount, then the necessary payment will be computer in terms of the monthly interest rate (int:
int = IntRate/100*(IntPeriod/12)
Payment = LoanAmount *
(int*(1+int)^(Term*12/IntPeriod))/((1+int)^(Term*12/IntPeriod)-1)