Discounted Cumulative Interest Calculation in One Cell

[bran8989]

I am trying to discount cumulative interest over a period of time.

Say I have an original mortgage for $100,000 and a term of 60 months, then the borrower prepays on month 1 for $50,000. I want to know what the cumulative interest lost over that period is at a discounted rate of 15%.

I want to do this without having to build an amortization table, ideally want to do it using a formula in one cell.

Thanks

 

[James006]

Hello,

Which formula are you using to calculate interest ...???

 

[bran8989]

I'm using CUMIPMT.

 

[bran8989]

The problem I'm having is CUMIPMT is really just the combined cumulative interest undiscounted. What I really need is a way to discount each period of lost interest then sum the total.

 

[James006]

Hi,

In terms of the formula you are using to calculate interest ...

1. Do you have a fixed annuity ...

2. Or is it interest on remaining outstanding capital ?