Giúp đỡ về hàm tính lãi theo số dư giảm dần

ngocxit000

Thành viên mới
Tham gia ngày
28 Tháng một 2007
Bài viết
36
Được thích
2
Điểm
665
Tuổi
20
Mình có file tính lãi nhưng cứ mỗi phát sinh mình phải chia ra tính thủ công ở 1 sheet xong rồi gõ lại lãi vào 1 sheet.
Ví dụ: cột số 4 là tiền lãi cần tính căn cứ gốc & kỳ hạn vay & lãi suất.

1234
Gốc tiền vayKỳ hạn (tháng)Lãi suất (%)Tiền lãi dự kiến (đồng)
11,000,000​
12.021
1,234,110​

Bạn nào giúp mình công thức với
 

File đính kèm

Maika8008

Thành viên từ sao Hỏa
Tham gia ngày
12 Tháng sáu 2020
Bài viết
326
Được thích
251
Điểm
60
Mình có file tính lãi nhưng cứ mỗi phát sinh mình phải chia ra tính thủ công ở 1 sheet xong rồi gõ lại lãi vào 1 sheet.
Ví dụ: cột số 4 là tiền lãi cần tính căn cứ gốc & kỳ hạn vay & lãi suất.

1234
Gốc tiền vayKỳ hạn (tháng)Lãi suất (%)Tiền lãi dự kiến (đồng)
11,000,000​
12.021
1,234,110​

Bạn nào giúp mình công thức với
Bạn cứ thêm 1 cột gốc phải trả hàng tháng. Theo đó tiền lãi hàng tháng được tính BẰNG (gốc còn lại tháng trước TRỪ gốc phải trả tháng này) NHÂN lãi suất. Excel có hàm đó nhưng bạn làm như trên cho nó dễ nhìn, dễ kiểm soát trả nợ
 

Nhattanktnn

Thành viên thường trực
Tham gia ngày
11 Tháng mười một 2016
Bài viết
388
Được thích
309
Điểm
210
Mình có file tính lãi nhưng cứ mỗi phát sinh mình phải chia ra tính thủ công ở 1 sheet xong rồi gõ lại lãi vào 1 sheet.
Ví dụ: cột số 4 là tiền lãi cần tính căn cứ gốc & kỳ hạn vay & lãi suất.

1234
Gốc tiền vayKỳ hạn (tháng)Lãi suất (%)Tiền lãi dự kiến (đồng)
11,000,000​
12.021
1,234,110​

Bạn nào giúp mình công thức với
Mã:
F4=SUMPRODUCT((B4-(B4/C4)*(ROW(INDIRECT("1:"&C4))-1))/365*D4/100*30)
Mình làm theo cách tính của bạn,
Thắc mắc: dòng 3, 1năm =360 ngày, 1tháng = 31 ngày. Khác 2 dòng trên
 

VetMini

Chuyên gia GPE
Tham gia ngày
21 Tháng mười hai 2012
Bài viết
9,516
Được thích
11,399
Điểm
1,560
Tính lãi là nghề của tôi. Nhưng rất tiếc là tôi nhìn vào cái file của thớt chả hiểu muốn tính cái gì.
File này hình như truyền lại từ thời Lotus 123, cổ đại hơn cả Excel 4.
 

bebo021999

Thành viên gạo cội
Tham gia ngày
26 Tháng một 2011
Bài viết
3,692
Được thích
5,839
Điểm
560
Nơi ở
TPHCM, Gò Vấp
Excel có hàm ISPMT để tính nha bạn. Nó tính theo tiêu chuẩn 30 ngày/ tháng, 360 ngày/năm

=SUMPRODUCT(ISPMT(D4/100/12,0,C4,B4-B4/C4*(ROW(INDIRECT("1:"&C4))-1)))

Nếu bên bảng tính tay của bạn tính trên cơ sở này thì kết quả sẽ giống nhau.

Chuẩn 30/360 là chuẩn hiện nay hợp nhất trong tài chính nhé. Các NH đều tính theo chuẩn này
 

File đính kèm

VetMini

Chuyên gia GPE
Tham gia ngày
21 Tháng mười hai 2012
Bài viết
9,516
Được thích
11,399
Điểm
1,560
Excel có hàm ISPMT để tính nha bạn. Nó tính theo tiêu chuẩn 30 ngày/ tháng, 360 ngày/năm
...
Có hai hàm để tính là IPMT và ISPMT.
Thớt không diễn tả rõ là trả tiền theo kiểu nào cho nên chưa xác định được hàm nào để dùng.
1. Chia số tiền vốn + lãi thành N kỳ và mỗi kỳ trả một số tiền nhất định: IPMT
2. Chia số tiền vốn thành N kỳ, và mỗi kỳ trả số này cộng với lãi trong kỳ: ISPMT
Chỉ nhìn file thì đoán có lẽ kiểu 2.

Chú thích: loại tính này người ta dùng bảng hoàn vốn để tính chứ ít khi vèo một lần ra cái tổng.

...
Nếu bên bảng tính tay của bạn tính trên cơ sở này thì kết quả sẽ giống nhau.
Chuẩn 30/360 là chuẩn hiện nay hợp nhất trong tài chính nhé. Các NH đều tính theo chuẩn này
Thớt tính theo chuẩn nghịch đảo của 365/360 (tức là 360/365, lãi thực tế nhỏ hơn lãi năm 1 chút). Đó là tại sao ở bài #4 tôi nói là dòm file chả hiểu gì cả. Ai cho vay ăn lãi lại chịu thiệt như vậy?

Nếu tính theo kiểu 2 ở trên và lãi tháng 30/360 thì công thức là
=SUMPRODUCT(ISPMT($D4/100/12, ROW(INDIRECT("1:"&$C4))-1, $C4, -$B4))
hoặc:
{=SUM(ISPMT($D4/100/12, ROW(INDIRECT("1:"&$C4))-1, $C4, -$B4))} --> hàm mảng CSE
giải thích: ROW(INDIRECT("1:"&$C4))-1 --> con số -1 ở đây là vì hàm ISPMT này coi kỳ đầu tiên là kỳ thứ 0)

Nếu tính theo thớt (30/365) thì công thức là
=SUMPRODUCT(ISPMT($D4/100/365*30, ROW(INDIRECT("1:"&$C4))-1, $C4, -$B4))
 
Lần chỉnh sửa cuối:

ngocxit000

Thành viên mới
Tham gia ngày
28 Tháng một 2007
Bài viết
36
Được thích
2
Điểm
665
Tuổi
20
Tính lãi là nghề của tôi. Nhưng rất tiếc là tôi nhìn vào cái file của thớt chả hiểu muốn tính cái gì.
File này hình như truyền lại từ thời Lotus 123, cổ đại hơn cả Excel 4.
vâng, file này dùng 15 năm trước rồi ạ
Bài đã được tự động gộp:

Có hai hàm để tính là IPMT và ISPMT.
Thớt không diễn tả rõ là trả tiền theo kiểu nào cho nên chưa xác định được hàm nào để dùng.
1. Chia số tiền vốn + lãi thành N kỳ và mỗi kỳ trả một số tiền nhất định: IPMT
2. Chia số tiền vốn thành N kỳ, và mỗi kỳ trả số này cộng với lãi trong kỳ: ISPMT
Chỉ nhìn file thì đoán có lẽ kiểu 2.

Chú thích: loại tính này người ta dùng bảng hoàn vốn để tính chứ ít khi vèo một lần ra cái tổng.


Thớt tính theo chuẩn nghịch đảo của 365/360 (tức là 360/365, lãi thực tế nhỏ hơn lãi năm 1 chút). Đó là tại sao ở bài #4 tôi nói là dòm file chả hiểu gì cả. Ai cho vay ăn lãi lại chịu thiệt như vậy?

Nếu tính theo kiểu 2 ở trên và lãi tháng 30/360 thì công thức là
=SUMPRODUCT(ISPMT($D4/100/12, ROW(INDIRECT("1:"&$C4))-1, $C4, -$B4))
hoặc:
{=SUM(ISPMT($D4/100/12, ROW(INDIRECT("1:"&$C4))-1, $C4, -$B4))} --> hàm mảng CSE
giải thích: ROW(INDIRECT("1:"&$C4))-1 --> con số -1 ở đây là vì hàm ISPMT này coi kỳ đầu tiên là kỳ thứ 0)

Nếu tính theo thớt (30/365) thì công thức là
=SUMPRODUCT(ISPMT($D4/100/365*30, ROW(INDIRECT("1:"&$C4))-1, $C4, -$B4))
Em tính lãi theo số dư giảm dần bác ạ.
Bài đã được tự động gộp:

Excel có hàm ISPMT để tính nha bạn. Nó tính theo tiêu chuẩn 30 ngày/ tháng, 360 ngày/năm

=SUMPRODUCT(ISPMT(D4/100/12,0,C4,B4-B4/C4*(ROW(INDIRECT("1:"&C4))-1)))

Nếu bên bảng tính tay của bạn tính trên cơ sở này thì kết quả sẽ giống nhau.

Chuẩn 30/360 là chuẩn hiện nay hợp nhất trong tài chính nhé. Các NH đều tính theo chuẩn này
Thanks bác
 
Top Bottom