Possible Variable in Rate Function
 

I am trying to resolve an issue with a "RATE" Function. I know that it is
correct, but I would like to introduce a variable. This is to have a holiday
for 6 months i.e. nil payment. To clarify:

I am trying to calculate the interest rate assumming that at the beginning
of a repayment structure there is an "holiday payment". "Holiday payment" -
This mean at the beginning of the term there would be as in this case 6
months where there is no payments. Monthy payments begin in Month 7 until
month 42 ie 36 Months.


Data Months Paid Data Holiday Data Total
Payment holiday in months 6 6
Months 36 36
Total Term of Loan 36 6 42
Monthly payments £24,000.00 £0.00 £20,571.43
Loan £705,270.00 £705,270.00 £705,270.00

Rate Calculated 13.69% -1200.00% 11.78%

Interest Paid £158,730.00 -£705,270.00 £158,730.00


Interest per Month £4,409.17 £3,779.29
Capital per Month £19,590.83 £16,792.14
£24,000.00 £20,571.43



The attached spreedsheet explains it better and shows the way in which the
Function is written.

Thank you for any help you can give.