Xin trợ giúp VBA tính lãi khi cho vay

[Dauthivan]

Em thực sự cảm ơn các anh chị trên diễn đàn đã giúp đỡ em rất nhiều trong thời gian qua. Em đã có lần nhờ các anh chị trên diễn đàn giúp đỡ công thức tổng hợp dữ liệu cho vay, bên em phải làm diễn giải cho vay khoảng 100 công ty (tức là khoảng 100 sheet em đính kèm ví dụ). Khó nhất của em trong diễn giải là khoản vay nào trước thì phải trả trước (Các lần vay có thể lãi suất khác nhau). Em phải xác định từ ngày cho vay đến ngày trả nợ gốc từng lần thì số dư cho vay theo từng lãi. Mong các anh chị giúp đỡ

 

[Phanhanhdai]

Mình mới tập VBA nên chưa thành thạo cú pháp của nó. Mình cho bạn 2 bài của mình, nếu bạn viết VBA bạn xem có thể học được gì trong đó không
bài viết của anh ndu96081631. và [http://www.giaiphapexcel.com/forum/showthread.php?t=13095] Bài viết của anh boyxin
Mình đồng ý với bạn cũng có nhiều khi xử lý bằng công thức sẽ rất phức tạp. Nhiều khi file quá nặng, trong trường hợp này nên dùng VBA, trên diễn đàn các anh PhanTuHuong, boyxin, ndu96081631,hoangdanh282vn, anhtuan1066, Võ Tuấn Kiệt ...là các chuyên gia hàng đầu về lập trình VBA, các anh ấy kiến thức rất sâu bạn thử nhờ các anh ấy.
Thưa các anh, chị trên diễn đàn VBA là ngôn ngữ lập trình ứng ụng vào EXCEL rất hay, em rất mong các anh, chị cùng trao đổi qua những ví dụ để mọi người trên diễn đàn có thể học tập. Rất mong các anh chị giúp đỡ ví dụ này!

 

[cadafi]

Em thực sự cảm ơn các anh chị trên diễn đàn đã giúp đỡ em rất nhiều trong thời gian qua. Em đã có lần nhờ các anh chị trên diễn đàn giúp đỡ công thức tổng hợp dữ liệu cho vay, bên em phải làm diễn giải cho vay khoảng 100 công ty (tức là khoảng 100 sheet em đính kèm ví dụ). Khó nhất của em trong diễn giải là khoản vay nào trước thì phải trả trước (Các lần vay có thể lãi suất khác nhau). Em phải xác định từ ngày cho vay đến ngày trả nợ gốc từng lần thì số dư cho vay theo từng lãi. Mong các anh chị giúp đỡ

Chào bạn, thực sự mà nói nhìn vào file của bạn tôi cũng hơi hình dung ra yêu cầu của bạn là gì. Tuy nhiên với kiểu bố trí file của bạn như thế tôi không tài nào làm được.

Bạn hãy hình dung như thế này:

Dữ liệu hàng ngày phát sinh phải được ghi nhận vào 1 sheet gọi là sheet Data (hoặc bạn muốn đặt tên gì tùy ý).

Dữ liệu sẽ phải được thiết kế và bố trí sao cho khoa học và thể hiện đầy đủ các nội dung cần trích xuất, lọc, phân tích.

Nếu bạn đồng ý như vậy thì chúng ta sẽ bắt tay cùng tạo một data như thế và từ từ sẽ hoàn chỉnh.

 

[Nguyễn Xuân Sơn]

Không biết bạn làm NH nào, nhưng tôi thấy các NH đều có phân hệ cho vay ở phần mền hệ thống máy tính của mỗi NH rồi, việc tính lãi cho vay cũng có cả trong phần mền này, ở các QTD nhân dân cũng đã có phần mền kế toán cho vay. Vì vậy việc tính lãi cho vay ( kể cả lãi dự trả ... ) đều được thực hiện hằng ngày và khi có P/sinh giao dịch. Còn nếu bạn muốn theo dõi riêng trên Excel cho chủ động thì fai mẫu bạn đưa lên tôi nghĩ chưa khoa học và các tiêu chí chưa rõ ràng. Chẳng hạn như mục diễn giải, thì bạn nên cho vào là đối tượng vay còn ngày vay, kỳ hạn ( hạn trả ) hoặc ngày PS nghiệp vụ vay, trả, tính lãi phải trả sẽ cho riêng ra các trường ( cột ) khác ..... . Để tiết giảm thời gian, bạn nghiên cứu lại và bố trí biểu mẫu sao cho hợp lý đưa lên diễn đàn để mọi người tham khảo và cùng ghiải quyết.

 

[Mr Okebab]

Được anh quan tâm giúp đỡ thế thì còn gì bằng, anh có thể bố trí lại cũng được, nếu em có được như vậy thì công việc sẽ bớt đi rất nhiều thời gian. Xin chúc anh và gia đình vui vẻ, hạnh phúc

Bạn hãy hình dung như thế này:

Dữ liệu hàng ngày phát sinh phải được ghi nhận vào 1 sheet gọi là sheet Data (hoặc bạn muốn đặt tên gì tùy ý).

Dữ liệu sẽ phải được thiết kế và bố trí sao cho khoa học và thể hiện đầy đủ các nội dung cần trích xuất, lọc, phân tích.

thế này được chưa Cadafi ??

NGAYTHANG|KHACHHANG|NGHIEPVU|SOTIEN|LAISUAT
12/05/2008|A|Vay|10.000.000.000|8,00%
13/05/2008|A|Vay|10.000.000.000|8,00%
13/05/2008|A|Vay|5.000.000.000|8,20%
16/05/2008|A|Vay|10.000.000.000|8,00%
16/05/2008|A|Vay|5.000.000.000|8,20%
17/05/2008|A|Tra|12.000.000.000|

Việck khoản trả đó trả cho các khoản nào (ngày nào, lãi suất bao nhiêu) thì sẽ tính toán ở bên Report.

Thân!

 

[cadafi]

thế này được chưa Ca_dafi ??

NGAYTHANG|KHACHHANG|NGHIEPVU|SOTIEN|LAISUAT
12/05/2008|A|Vay|10.000.000.000|8,00%
13/05/2008|A|Vay|10.000.000.000|8,00%
13/05/2008|A|Vay|5.000.000.000|8,20%
16/05/2008|A|Vay|10.000.000.000|8,00%
16/05/2008|A|Vay|5.000.000.000|8,20%
17/05/2008|A|Tra|12.000.000.000|

Sẽ có trường hợp cùng một ngày có hai khoản vay cùng lãi suất đối với cùng 1 khách hàng, lúc đó chúng ta biết khoản nào vay trước để trừ ra đây?

Thông thường, đối với các khoản vay ngân hàng, việc rút vốn vay đi chung với chứng từ là Giấy Nhận Nợ (hoặc chứng từ có tên gọi khác nhưng cùng chức năng). Và chứng từ này được đánh số để phân biệt cái nào tới trước, cái nào tới sau, chí ít cũng xác định được trong một ngày! Có những ngân hàng, chỉ cần nhìn vào số chứng từ là biết được ngay chứng từ đó phát sinh ngày nào, và theo thứ tự nào?

Vấn đề em muốn xoáy vào chỗ này là số chứng từ (Document Nbr/Refference Nbr). Nếu thiết kế tốt thì chỉ cần tham chiếu số chứng từ, Mã khách hàng, loại chứng từ và/hoặc Mức lãi suất là ta đã giải quyết được bài toán này!

 

[Mr Okebab]

Sẽ có trường hợp cùng một ngày có hai khoản vay cùng lãi suất đối với cùng 1 khách hàng, lúc đó chúng ta biết khoản nào vay trước để trừ ra đây?

Thông thường, đối với các khoản vay ngân hàng, việc rút vốn vay đi chung với chứng từ là Giấy Nhận Nợ (hoặc chứng từ có tên gọi khác nhưng cùng chức năng). Và chứng từ này được đánh số để phân biệt cái nào tới trước, cái nào tới sau, chí ít cũng xác định được trong một ngày! Có những ngân hàng, chỉ cần nhìn vào số chứng từ là biết được ngay chứng từ đó phát sinh ngày nào, và theo thứ tự nào?

Vấn đề em muốn xoáy vào chỗ này là số chứng từ (Document Nbr/Refference Nbr). Nếu thiết kế tốt thì chỉ cần tham chiếu số chứng từ, Mã khách hàng, loại chứng từ và/hoặc Mức lãi suất là ta đã giải quyết được bài toán này!

OK, vậy thì chỉ cần thay Nghiệp vụ = Chứng từ là OK rồi. Chứng từ sẽ thể hiện nghiệp vụ (Loại chứng từ) và số thứ tự của nghiệp vụ.

Loại Chứng từ và STT không nên tách ra thành 2 cột.

OK???

Thân!

 

[Dauthivan]

Bạn hãy hình dung như thế này:

Dữ liệu hàng ngày phát sinh phải được ghi nhận vào 1 sheet gọi là sheet Data (hoặc bạn muốn đặt tên gì tùy ý).

Dữ liệu sẽ phải được thiết kế và bố trí sao cho khoa học và thể hiện đầy đủ các nội dung cần trích xuất, lọc, phân tích.

Nếu bạn đồng ý như vậy thì chúng ta sẽ bắt tay cùng tạo một data như thế và từ từ sẽ hoàn chỉnh.

Em nghĩ đây cũng là một bài toán phức tạp, em rất mong mọi người đặc biệt là các bác thông thạo về VBA giúp đỡ.
Trong thời gian chờ các bác trên diễn đàn giúp đỡ. Mọi người nếu ai có phần mềm tính lãi vay cho em xin để em kiểm tra kết quả hàng ngày
Em xin cảm ơn nhiều

 

[cadafi]

Đọc kỹ hướng dẫn sử dụng trước khi dùng.

Em thực sự cảm ơn các anh chị trên diễn đàn đã giúp đỡ em rất nhiều trong thời gian qua. Em đã có lần nhờ các anh chị trên diễn đàn giúp đỡ công thức tổng hợp dữ liệu cho vay, bên em phải làm diễn giải cho vay khoảng 100 công ty (tức là khoảng 100 sheet em đính kèm ví dụ). Khó nhất của em trong diễn giải là khoản vay nào trước thì phải trả trước (Các lần vay có thể lãi suất khác nhau). Em phải xác định từ ngày cho vay đến ngày trả nợ gốc từng lần thì số dư cho vay theo từng lãi. Mong các anh chị giúp đỡ

Em thực sự rất cần giải quyết bài toán này, mong bác cao thủ VBA trên diễn đàn giúp đỡ (hoặc gợi ý cách làm dùng công thức để bài toán có thể được giải quyết đơn giản hơn).

Vi thời gian hạn hẹp. Thuật toán có thể có sai sót, các bạn test và kiểm tra lại nhé (hi vọng là không có sai!)

Lưu ý một số điểm như sau:
Chương trình phân thành hai mảng:
a. Cập nhật số liệu (từ sheet("Menu") click vào nút [Cập nhật số liệu]

Lưu ý: Số chứng từ excel tự tính, tự nhảy.
Các bạn có thể nhập liệu trực tiếp từ sheet("Data"), hoặc thông qua Form này.
Tuy nhiên, phải tuân thủ format của sheet("Data")

b. Xử lý số liệu (từ sheet("Menu") click vào nút [Xử lý số liệu]

- Chọn Mã khách hàng ==> click nút [Thực hiện]

- Để tính toán các khoản trả vay áp cho chứng từ vay (giấy nhận nợ nào) thì click vào nút [Tính toán các khoản trả vay và vay tương ứng]

Lưu ý: đây chỉ là form để xem báo cáo, chưa lưu ra sheet, muốn lưu ra sheet và in thì click vào nút [Copy ra excel để lưu trữ]. Chương trình sẽ hỏi bạn gõ tên sheet cần lưu, nếu bỏ trống, excel sẽ tự lấy Mã khách hàng làm tên sheet (dĩ nhiên là có kiểm tra sự tồn tại của sheet có tên này)

Và đây là kết quả cuối cùng lưu ra sheet.

Lưu ý quan trọng nhất: Ba sheet [Menu], [Data], [System] không được xóa. Nếu xóa thì chương trình không chạy được.

Như vậy việc còn lại của bạn là nhập vào sheet data phần dữ liệu của bạn theo format của sheet data là xong, nhập thoải mái mà không cần bận tâm khoản trả vay này áp cho khoản vay nào!? Excel tự làm hết rồi!

Các bạn xem file đính kèm nhé!

 

[ndu96081631]

Hỏi ngoài lề 1 chút với bạn ca_dafi: Tôi thấy trong Forms của bạn đang gõ tiếng Việt Unicode trên các Command Button! Sao có thể làm được như vậy nhỉ?

 

[cadafi]

Hỏi ngoài lề 1 chút với bạn ca_dafi: Tôi thấy trong Forms của bạn đang gõ tiếng Việt Unicode trên các Command Button! Sao có thể làm được như vậy nhỉ?

Cách đơn giản nhất là khi tạo xong một commandbutton trên form, anh click chuột trái vào ngay commandbutton đó và gõ tiếng Việt, không gõ vào phần Caption trong properties của commandbutton đó.
--------------------------------------------------------------------------------
Cách phức tạp hơn chút xíu: Giả sử ô A1 của sheet("SysTem") em gõ vào = 'Thực Hiện"
Xử lý lúc UserForm_Activate như sau:

Sub UserForm_Activate()
Me.CommandButton1.Caption=sheets("System").[A1]
End Sub

Có nghĩa là ta có thể kiểm soát được caption của các nút trên form qua 1 sheet; chỉnh sửa trực tiếp trên sheet (mà gõ tiếng Việt trên sheet thì dễ hơn nhiều so với trên Form phải không anh!?). Đây cũng là cách chuyển đổi giao diện form bằng tiếng Anh hay tiếng Việt đấy anh! Anh xem ví dụ file đính kèm nhé!

 

[Mr Okebab]

Cảm ơn Cadafi nhé, đang làm dở 1 File nho nhỏ thôi, tuy nhien do nhiều việc nên chưa xong, nay thấy cậu làm "hoành tráng " quá nên thôi (Phần tính toán thì đơn giản, phần trang trí và viết mấy cái code cho listbox hơi mất thời gian)

Tuy nhiên nếu dùng Listview thì sẽ đẹp hơn nữa.

Cảm ơn nhiều

Thân!

 

[ndu96081631]

Cách đơn giản nhất là khi tạo xong một commandbutton trên form, anh click chuột trái vào ngay commandbutton đó và gõ tiếng Việt, không gõ vào phần Caption trong properties của commandbutton đó.
--------------------------------------------------------------------------------
Cách phức tạp hơn chút xíu: Giả sử ô A1 của sheet("SysTem") em gõ vào = 'Thực Hiện"
Xử lý lúc UserForm_Activate như sau:

Sub UserForm_Activate()
Me.CommandButton1.Caption=sheets("System").[A1]
End Sub

Có nghĩa là ta có thể kiểm soát được caption của các nút trên form qua 1 sheet; chỉnh sửa trực tiếp trên sheet (mà gõ tiếng Việt trên sheet thì dễ hơn nhiều so với trên Form phải không anh!?). Đây cũng là cách chuyển đổi giao diện form bằng tiếng Anh hay tiếng Việt đấy anh! Anh xem ví dụ file đính kèm nhé!

Hay à nha! Trước giờ gõ chử tiếng Việt trên cmd button trên sheet không được, cứ tưởng trong UserForms cũng thế... Ai ngờ...
Tôi còn có 1 cách khác là đặt name... Ví dụ Name Lb1 = "Thực hiện" ... sau đó dùng code:

CommandButton1.Caption = Evaluate("Lb1")

(Do trước giờ không biết có thể gõ trực tiếp được nên đã làm theo kiểu rách việc như vầy... Ẹc.. Ẹc.... )

 

[Phanhanhdai]

Chưa tính được lãi vay

Em đã áp dụng bài viết của anh Kiệt để áp dụng, cách của anh đã xác định được dư nợ của mỗi khoản nhưng mục tiêu cuối cùng tính lãi của Chủ đầu tư phải trả NH thì chưa xác định được (Vì kết quả cuối cùng mới xác định được dư nợ tính đến ngày trả cuối cùng, còn các ngày trả vốn trung gian ảnh hưởng trực tiếp đến lãi phải trả thì chưa tính đến). Khi trả nợ vào một ngày nào đó thì phần vốn vay từ ngày ấy trở đi sẽ giảm do đó lãi vay giảm. Rất mong anh và mọi người có thể góp ý.

 

[cadafi]

Em đã áp dụng bài viết của anh Kiệt để áp dụng, cách của anh đã xác định được dư nợ của mỗi khoản nhưng mục tiêu cuối cùng tính lãi của Chủ đầu tư phải trả NH thì chưa xác định được (Vì kết quả cuối cùng mới xác định được dư nợ tính đến ngày trả cuối cùng, còn các ngày trả vốn trung gian ảnh hưởng trực tiếp đến lãi phải trả thì chưa tính đến). Khi trả nợ vào một ngày nào đó thì phần vốn vay từ ngày ấy trở đi sẽ giảm do đó lãi vay giảm. Rất mong anh và mọi người có thể góp ý.

Thứ nhất, bạn phải xác định: số tiền khách hàng trả có bao gồm tiền lãi hay không? - Cái này quan trọng nhất;
Thứ hai: Tại sao không tính lãi ngay khi khách hàng trả tiền, mà phải đợi rồi tính một lần?

 

[Dauthivan]

Thứ nhất, bạn phải xác định: số tiền khách hàng trả có bao gồm tiền lãi hay không? - Cái này quan trọng nhất;
Thứ hai: Tại sao không tính lãi ngay khi khách hàng trả tiền, mà phải đợi rồi tính một lần?

Bọn em cho vay ví dụ tối đa là 30 tỷ đồng. Các khoản vay có thể được vay, trả thành nhiều lần, các lần trả trong tháng là trả gốc (không bao gồm lãi). Lãi đầu tháng sau sẽ tính trả cho các lần vay tháng trước anh ạh.
Ví dụ Hạn mức là 30 tỷ (tối đa được vay là 30 tỷ), ngày 1/9/2008 vay 3 tỷ, ngày 3/9/2008 vay 5 tỷ, ngày 10/8/2008 trả 4 tỷ (Em chỉ ví dụ giả định thôi). Tức là tóm lại khống chế con số tối đa, còn có thể vay, trả thành nhiều lần anh ạh.

 

[Phanhanhdai]

Xin nhờ chuyển viết VBA sang đuôi dạng XLA

Em cảm ơn mọi người đã giúp đỡ em, nhờ có sự giúp đỡ của mọi người em đã bớt được rất nhiều trong công việc. File của anh Kiệt rất hữu ích. Nhưng mọi người trong cơ quan em thích diệt bằng Bkav thành ra chương trình hay lỗi. Em nhờ mọi người chuyển giúp em ghi phần viết VBA thành đuôi xla (Bời em thấy Bkav không diệt khi chuyển cách sử dụng như vậy). Em xin chân thành cảm ơn!

 

[longhm]

Khi cap nhat chung tu thi excel bao "could not load an object because it is not avalable on this machine". Xu ly van de nay the nao, cadifi giup voi!