# PPMT Function with varies interest rate

1. ## PPMT Function with varies interest rate

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  Register To Reply

2. ## RE: PPMT Function with varies interest rate

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))

"ExcelMonkey" wrote:

> 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
>  Register To Reply