Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Payment calculation w/ back interest | Excel Discussion (Misc queries) | |||
Interest Functions | Excel Worksheet Functions | |||
Function for interest rate over several years | Excel Worksheet Functions | |||
Cash investment tracking when receiving variable interest rate | Excel Discussion (Misc queries) | |||
Calculating Interest where rate changes per quarter | Excel Discussion (Misc queries) |