Anyone know of a way to calculate the repayment schedule for a loan with
fixed interest rate and term, and a deferred payment schedule? For example,
figuring the payments on a 40-year loan for the amount of $10 million at 6%,
if the payments were only made in years 26 through 40. Functions in Excel,
from what I see, require payments to begin in year 1. I can use the NPV
function to try to get in the ballpark, but that's a slow, interative process.

Thanks for the help.

Yes, there is a way to calculate the repayment schedule for a loan with deferred payments using Excel. You can use the function in Excel to calculate the payment amount for a loan with a fixed interest rate and term. However, since the payments are deferred, you will need to adjust the formula to account for the deferred period.

Here are the steps to calculate the repayment schedule for a loan with deferred payments:

1. Open a new Excel worksheet and enter the following information in cells A1 to A4:
   
   Loan amount: $10,000,000
   Interest rate: 6%
   Term: 40 years
   Deferred period: 25 years
   
2. In cell A5, enter the formula to calculate the payment amount for the loan:
   
   
   Formula:

   =PMT(A2/12,A3*12,-A1) 

   This formula uses the
   Formula:

   PMT 

   function to calculate the payment amount for the loan. The first argument is the interest rate divided by 12 (since the loan term is in years and the payment period is in months). The second argument is the total number of payments, which is the loan term multiplied by 12. The third argument is the loan amount, which is negative since it represents a cash outflow.
   
3. Copy the formula in cell A5 to cells B5 to N5. These cells represent the payment periods for years 26 to 40.
   
4. In cell O5, enter the formula to calculate the total interest paid over the life of the loan:
   
   
   Formula:

   =SUM(B5:N5)*-12*A3-A1 

   This formula uses the
   Formula:

   SUM 

   function to add up the payment amounts for years 26 to 40, multiplies the sum by -12 (to convert from monthly to annual payments), multiplies by the loan term in years, and subtracts the loan amount to get the total interest paid.
   
5. In cell P5, enter the formula to calculate the total payments over the life of the loan:
   
   
   Formula:

   =SUM(B5:N5)*-12*A3 

   This formula uses the
   Formula:

   SUM 

   function to add up the payment amounts for years 26 to 40, multiplies the sum by -12 (to convert from monthly to annual payments), and multiplies by the loan term in years to get the total payments.
   
6. Format cells A1 to P5 as currency with two decimal places.

These steps should give you the repayment schedule for a loan with deferred payments. Let me know if you have any questions or need further assistance.

Hi Paul

Would it not be the same as calculating the loan over 15 years, but starting
with a principal which was
10,000,000*(1.06)^25

i.e. by the time payments begin, the accrued Loan = Interest would be
$42,918,707.20
Paying this off over 15 years at 6% interest would give annual payments of
$3,389,401.08


--
Regards
Roger Govier

"Paul 022" <Paul wrote in message
...
Anyone know of a way to calculate the repayment schedule for a loan with
fixed interest rate and term, and a deferred payment schedule? For
example,
figuring the payments on a 40-year loan for the amount of $10 million at
6%,
if the payments were only made in years 26 through 40. Functions in
Excel,
from what I see, require payments to begin in year 1. I can use the NPV
function to try to get in the ballpark, but that's a slow, interative
process.

Thanks for the help.

Good point, Roger. That works - thanks for the help!

"Roger Govier" wrote:

Hi Paul

Would it not be the same as calculating the loan over 15 years, but starting
with a principal which was
10,000,000*(1.06)^25

i.e. by the time payments begin, the accrued Loan = Interest would be
$42,918,707.20
Paying this off over 15 years at 6% interest would give annual payments of
$3,389,401.08


--
Regards
Roger Govier

"Paul 022" <Paul wrote in message
...
Anyone know of a way to calculate the repayment schedule for a loan with
fixed interest rate and term, and a deferred payment schedule? For
example,
figuring the payments on a 40-year loan for the amount of $10 million at
6%,
if the payments were only made in years 26 through 40. Functions in
Excel,
from what I see, require payments to begin in year 1. I can use the NPV
function to try to get in the ballpark, but that's a slow, interative
process.

Thanks for the help.