CPR in mortgage loans

[grezek]

Hi All,

I'm creating a schedule of mortgage payments from a snap shot in time.  I know the total loan amount, total number of monthly periods, the interest rate (this is a fixed rate loan), the unpaid principal balance, principal payment and interest payment at all periods.  I am given an "average" CPR.  Is it possible to construct a column of CPR rates for the loan?  Any suggestions are appreciated.  Thanks.  -- George

[PaigeMiller]

For those of us who don't know, could you please tell us what CPR means? Thanks!

[grezek]

Sorry, I don't like it when people give abbreviations myself. It's constant
prepayment rate. -- George

[Patrick]

As so often it would really help if you could provide some sample data together with the desired result in the form of a SAS data step that creates this data. 

I don't have sufficient subject matter expertise to just confidently answer your question without such sample data. 

I have copy/pasted what you wrote into chatGPT. Suggest you do the same and assess if the answer given is helpful/correct.

Spoiler

Yes, it is possible to construct a column of CPR (Conditional Prepayment Rate) rates for the loan given the total loan amount, total number of monthly periods, the interest rate, unpaid principal balance, principal payment, and interest payment at all periods. However, constructing this column may involve some complex calculations.

Here are the steps to construct a column of CPR rates:

1. Understand CPR: CPR is a measure of how quickly borrowers are expected to prepay their mortgages. It is expressed as a percentage of the remaining principal balance that is expected to be paid off each year. The "average" CPR you have is an average estimate of how quickly prepayments will occur over time.

2. Calculate Remaining Loan Balance: Start by calculating the remaining loan balance for each period. You can use the unpaid principal balance, which should be provided for each period. If you have the original loan amount, subtract the unpaid principal balance from it to get the remaining balance.

3. Estimate Prepayment Amount: Use the CPR to estimate the prepayment amount for each period. The formula is:

   java

   Prepayment Amount = CPR * Remaining Loan Balance

   The CPR rate is typically given on an annual basis, so you may need to adjust it for the monthly period you are calculating. For example, if you have a 6% annual CPR, you would use 0.5% (6% divided by 12) for a monthly calculation.

4. Calculate Monthly Payments: If you have the principal payment and interest payment for each period, you can calculate the total monthly payment. The sum of the principal and interest payments should equal the total payment for that period.

5. Determine New Principal Balance: The new principal balance for the next period can be calculated as follows:

   java

   New Principal Balance = Previous Principal Balance - Principal Payment - Prepayment Amount

   In this equation, "Previous Principal Balance" refers to the remaining principal balance at the beginning of the current period.

6. Repeat for Each Period: Repeat the above steps for each period in the loan.

By following these steps, you can create a column of CPR rates for the loan. Keep in mind that the accuracy of these calculations depends on the accuracy of the data and the CPR estimate you have. CPR rates can change over time, so it's important to use the appropriate estimate for each period. Additionally, you may want to use spreadsheet software like Microsoft Excel or Google Sheets to automate these calculations for a large dataset.