Amortize Prepayments (Flat months) with start and end date (which can be mid month).

[EPRICE]

Hello

I currently have a non array formula (much preferred) that calculated the amortisation of prepayments, however it is calculated by days. I would much prefer the first month and last month to pro-rata by days, but the months in the middle are flat months.

In the example below I would require the May and Jun figures to be flat.

Any help would be much appreciated?

Note - I am a first time Mr Excel user, so I apologise if the format isn't quiet right when posting spreadsheets!

Many thanks,

Quote Reply
Report

 

[Dave Patton]

N.B. we cannot work with an image. To post an extract of your sheet, see the forum's tool that is named XL2BB.

It may be easier to just allocate by day.

T202305a.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1								Allocate amount
2							Days	16	30	31	15
3		Start	End	Days		Amount	Daily	April	May	June	July
4		15-Apr-22	15-Jul-22	92		1,000,000		173,913.04	331,521.74	331,521.74	163,043.48
5		15-Apr-22	15-Jul-22	92		1,000,000	10,869.57	173,913.04	336,956.52	326,086.96	163,043.48
6
2d

Cell Formulas
Range	Formula
H4	H4	=H2/92*F4
I4:J4	I4	=($F$4-$H$4-$K$4)/2
K4	K4	=K2/D4*F4
D4:D5	D4	=C4-B4+1
G5	G5	=F5/D5
H5:K5	H5	=MAX(0, MIN(EOMONTH(H$3, 0), $C5) + 1 - MAX(H$3, $B5))*$G5