PPMT Function with varies interest rate

[Guest]

Does anyone know if its possible to use an existing function in Excel to
calculate a periodic PPMT % which adds up to 100% WHEN using varied int
rates. For example: I have a three period rate using 10%:

=-PPMT(10%,1,3,1) = 30.2%
=-PPMT(10%,2,3,1) = 33.2%
=-PPMT(10%,3,3,1) = 36.6%

Cleary this addds to 100.0% over the three periods. But when the rate jumps
1% each period from 10% to 11% to 12 % I get:

=-PPMT(10%,1,3,1) = 30.2%
=-PPMT(11%,2,3,1) = 33.2%
=-PPMT(12%,3,3,1) = 37.2%

Which addes up to 100.6%. How do you get around this in excel. Is there a
function that can handle this?

Thanks

 

[Guest]

When you change rates you have to recalculate the remainder as if it were a
new loan for whatever the remaining balance is, and for whatever the
remaining term is. In your example you need to use (put these in A1:A3)

=-PPMT(10%,1,3,1)
=-PPMT(11%,1,2,1-A1)
=-PPMT(12%,1,1,1-SUM(A1:A2))