Creating a loan calculator with annual payments instead of monthly

[frankday]

I am trying to create a loan calculator that shows the annual payment instead of the monthly payment. I've been trying to use pmt function but that's not working. As an example: the loan amount is $568,804, Intrest is 1.875% and it has an annual payment of $21,456.

The interest is $10,665
Principal is $10,791

 

[Dave Patton]

try the following

T202410a.xlsm
	A	B	C	D	E
1
2	Principal	568,804.00
3	Interest	0.01875
4	Payment	21,456.00
5	Term	36.998523
6
7
8	Payment #	Paymnent	Interest	Principal	568,804.00
9	1	21,456.00	10665.08	10,790.93	558,013.08
10	2	21,456.00	10462.75	10,993.25	547,019.82
11	3	21,456.00	10256.62	11,199.38	535,820.44
12	4	21,456.00	10046.63	11,409.37	524,411.08
4a

Cell Formulas
Range	Formula
B5	B5	=NPER(B3,-B4,B2)
E8	E8	=B2
B9:B12	B9	=$B$4
C9:C12	C9	=E8*$B$3
D9:D12	D9	=B9-C9
E9:E12	E9	=E8-D9