Excel
có nhiều hàm hữu dụng trong cuộc sống, bài viết này tôi muốn giới thiệu 4 hàm tính toán rất hay dùng trong lĩnh vực ngân hàng: FV và PMT ứng
dụng trong hoạt động gửi tiền còn PPMT và IPMT ứng dụng trong hoạt động
vay tiền ngân hàng.
Tải ví dụ tại đây: PMT VAY GUI TIEN final.xls
1. Hàm FV:
Hàm FV được dùng để xác định tổng số tiền mà bạn nhận được khi gửi một số tiền nhất định (định kỳ) vào một ngân hàng có lãi suất nhất định.
Tải ví dụ tại đây: PMT VAY GUI TIEN final.xls
1. Hàm FV:
Hàm FV được dùng để xác định tổng số tiền mà bạn nhận được khi gửi một số tiền nhất định (định kỳ) vào một ngân hàng có lãi suất nhất định.
|
Ví dụ:
Bạn muốn gửi số tiền là 500000 VND (định kỳ hàng tháng) vào ngân hàng A,
với lãi suất là 11,50% /năm, trong thời gian 5 năm. Tổng số tiền mà bạn
nhận được sau 5 năm sẽ được tính theo hàm FV(rate, nper, pmt, pv, type)
như hình 1.
1. 500.000đ là số tiền gỏi hàng tháng bằng nhau trong nper kỳ, và 500.000d là tham số pmt trong hàm.
2. pv không phải tổng số tiền mà là giá trị hiện tại (Present value). Ý nghĩa của [pv] trong hàm là số tiền có sẵn trong NH vào thời điểm bạn gởi 500.000 đ lần đầu tiên. Mặc định nếu bỏ qua tham số này nghĩa là số tiền ban đầu bằng không.
3. nper là số kỳ gởi tiền không phải là tổng thời gian gởi. Tổng thời gian gởi tính theo đơn vị gì? Trong khi kỳ có thể là tuần, là tháng, là quý, là năm.
4. rate đúng là lãi suất nhưng phải nói rõ là lãi suất quy đổi theo kỳ gởi tiền. Nếu kỳ là 1 tháng, rate phải tính cho tháng, kỳ là quý, rate phải tính cho quý 3 tháng.
Trong đó:
- rate : Lãi suất
- nper: Tổng thời gian gửi
- pv : Tổng số tiền
- type : Kiểu, có hai giá trị là 1 hoặc 0 (1 là thanh toán vào đầu tháng, 0 là thanh toán vào cuối tháng)
Có thể thấy rằng vì thời gian gửi ở chương trình này được đổi thành 60 tháng nên giá trị của tham số lãi suất cũng được chia cho 12 để đảm bảo thu được giá trị chính xác trong 5 năm. Bạn cũng cần phải đặt dấu trừ trước hàm FV để thu được kết quả là số dương.
Công thức: FV(rate, nper, pmt, [pv], [type] )
rate: lãi suất
nper - number of period: số kỳ thanh toán.
pmt - payment amount: số tiền thanh toán từng kỳ.
PV - optional: giá trị hiện tại (ban đầu).
Type (0/1) - optional: mặc định là 0 khi thanh toán vào cuối kỳ và là 1 khi thanh toán đầu kỳ.
Ví dụ 1: Giả định gửi tiền kiệm $1000, lãi suất ngân hàng 10%/năm, gửi kỳ hạn 24 tháng. Tính số tiền rút cuối kỳ.
Công thức: =-FV(10%/12,24,0,1000)
Kết quả: $1,220.39
Ví dụ 2: Giả định định kỳ hàng tháng gửi $100, lãi suất ngân hàng 10%/năm, trong suốt 24 tháng. Tính số tiền rút cuối kỳ.
Công thức: =-FV(10%/12,24,100)
Kết quả: $2,644.69
Ví dụ 3: Giả định gửi tiền kiệm $1000, lãi suất ngân hàng 10%/năm, gửi kỳ hạn 24 tháng và hàng tháng gửi thêm $100. Tính số tiền rút cuối kỳ.
Công thức: =-FV(10%/12,24,100,1000)
Kết quả: $3,865.08
2. Hàm PMT:
Hàm PMT có chức năng ngược với FV, đó là khi bạn đã biết trước số tiền nhận được, thời gian gửi tiền và lãi suất ngân hàng thì tổng số tiền bạn cần phải gửi vào là bao nhiêu sẽ được tính qua hàm PMT.
1. 500.000đ là số tiền gỏi hàng tháng bằng nhau trong nper kỳ, và 500.000d là tham số pmt trong hàm.
2. pv không phải tổng số tiền mà là giá trị hiện tại (Present value). Ý nghĩa của [pv] trong hàm là số tiền có sẵn trong NH vào thời điểm bạn gởi 500.000 đ lần đầu tiên. Mặc định nếu bỏ qua tham số này nghĩa là số tiền ban đầu bằng không.
3. nper là số kỳ gởi tiền không phải là tổng thời gian gởi. Tổng thời gian gởi tính theo đơn vị gì? Trong khi kỳ có thể là tuần, là tháng, là quý, là năm.
4. rate đúng là lãi suất nhưng phải nói rõ là lãi suất quy đổi theo kỳ gởi tiền. Nếu kỳ là 1 tháng, rate phải tính cho tháng, kỳ là quý, rate phải tính cho quý 3 tháng.
Trong đó:
- rate : Lãi suất
- nper: Tổng thời gian gửi
- pv : Tổng số tiền
- type : Kiểu, có hai giá trị là 1 hoặc 0 (1 là thanh toán vào đầu tháng, 0 là thanh toán vào cuối tháng)
Có thể thấy rằng vì thời gian gửi ở chương trình này được đổi thành 60 tháng nên giá trị của tham số lãi suất cũng được chia cho 12 để đảm bảo thu được giá trị chính xác trong 5 năm. Bạn cũng cần phải đặt dấu trừ trước hàm FV để thu được kết quả là số dương.
Công thức: FV(rate, nper, pmt, [pv], [type] )
rate: lãi suất
nper - number of period: số kỳ thanh toán.
pmt - payment amount: số tiền thanh toán từng kỳ.
PV - optional: giá trị hiện tại (ban đầu).
Type (0/1) - optional: mặc định là 0 khi thanh toán vào cuối kỳ và là 1 khi thanh toán đầu kỳ.
Ví dụ 1: Giả định gửi tiền kiệm $1000, lãi suất ngân hàng 10%/năm, gửi kỳ hạn 24 tháng. Tính số tiền rút cuối kỳ.
Công thức: =-FV(10%/12,24,0,1000)
Kết quả: $1,220.39
Ví dụ 2: Giả định định kỳ hàng tháng gửi $100, lãi suất ngân hàng 10%/năm, trong suốt 24 tháng. Tính số tiền rút cuối kỳ.
Công thức: =-FV(10%/12,24,100)
Kết quả: $2,644.69
Ví dụ 3: Giả định gửi tiền kiệm $1000, lãi suất ngân hàng 10%/năm, gửi kỳ hạn 24 tháng và hàng tháng gửi thêm $100. Tính số tiền rút cuối kỳ.
Công thức: =-FV(10%/12,24,100,1000)
Kết quả: $3,865.08
2. Hàm PMT:
Hàm PMT có chức năng ngược với FV, đó là khi bạn đã biết trước số tiền nhận được, thời gian gửi tiền và lãi suất ngân hàng thì tổng số tiền bạn cần phải gửi vào là bao nhiêu sẽ được tính qua hàm PMT.
|
Ví dụ:
Bạn tiếp tục muốn gửi tiền vào ngân hàng B với lãi suất 5,66%/ năm trong
khoảng thời gian 5 năm để thu được một khoản tiền là 59.707.554,34 VND
thì tổng số tiền mà bạn cần gửi vào ngân hàng là bao nhiêu? Ta dùng hàm
PMT để giải quyết bài toán trên như hình 2.
Cú pháp của hàm PMT tương tự như hàm FV, trong đó C2 là lãi suất/năm, C4 là thời gian (5 năm tương đương với 60 tháng), C5 là tổng số tiền mong muốn nhận được.
Hàm pmt() có 5 tham số rate, nper, pv, [fv], type:
1. Hàm pmt() tính ra số tiền bằng nhau phải gởi ngân hàng mỗi kỳ không phải tính tổng số tiền phải gởi.
2. Các tham số khác không được nói đến mà ngầm hiểu như hàm fv(), nên cũng sai tuốt luốt:
- nper là số kỳ gởi không phải thời gian gởi
- pv là present value = số tiền có sẵn trong ngân hàng tại thời điểm gởi tiền kỳ đầu tiên, thường là bằng không theo thí dụ cụ thể này. Mặc định của nó không bằng không.
- fv là giá trị tương lai (Future value), là số tiền mình mong mỏi sẽ nhận được sau khoảng thời gian tương đương nper kỳ gởi. Chính fv mới có mặc định bằng không nếu bỏ qua tham số.
- rate cũng phải quy về kỳ gởi.
GIẢI THÍCH TẠI SAO FV MẶC ĐỊNH BẰNG KHÔNG:
Pmt() được đặt ra để dùng tính số tiền bằng nhau phải trả mỗi kỳ trong nper kỳ, với lãi suất rate, cho 1 khoản vay hiện tại là pv; và đến cuối kỳ thứ nper, khoản vay được trả hết hoặc còn 1 khoản dư nợ fv.
Nói thêm:
Tham số nào có mặc định có thể bỏ qua thì trong cấu trúc hàm, nó được bao bằng dấu ngoặc vuông [].
Công thức: PMT(rate, nper, pv, fv, type)
Trong đó:
rate: lãi suất
nper: số kỳ
pv: giá trị hiện tại
fv: giá trị tương lai
type: thời điểm tính lãi (0: mặc định vào cuối kỳ, 1: đầu kỳ)
Ví dụ 1: Khoản vay $5,000 với lãi suất 7.5%/năm, kỳ hạn trong 2 năm. Tính khoản tiền trả gốc + lãi cố định vào đầu hàng tháng.
=Pmt(7.5%/12, 2*12, 5000, 0, 1)
Ví dụ 2: Khoản vay $8,000 với lãi suất 6%/năm, kỳ hạn trong 4 năm. Tính khoản tiền trả gốc + lãi cố định hàng tuần vào cuối kỳ.
=Pmt(6%/52, 4*52, 8000, 0, 0)
Ví dụ 3: Khoản vay $6,500 với lãi suất 5.25%/năm, kỳ hạn 10 năm. Tính khoản tiền trả gốc + lãi cố định vào cuối hàng năm.
=Pmt(5.25%/1, 10*1, 6500, 0, 0)
Ví dụ 4: Khoản vay $5,000 với lãi suất 8%/năm, kỳ hạn 3 năm, còn phải trả gốc sau 3 năm là $1,000. Tính khoản tiền trả gốc + lãi cố định vào cuối hàng tháng.
=Pmt(8%/12, 3*12, 5000, 1000, 0)
3. Hàm PPMT:
Hàm PPMT dùng để tính số tiền gốc mà bạn phải trả hàng tháng khi bạn vay tiền ở ngân hàng (đã biết trước lãi suất cho vay, số tiền vay và thời gian cho vay).
Cú pháp của hàm PMT tương tự như hàm FV, trong đó C2 là lãi suất/năm, C4 là thời gian (5 năm tương đương với 60 tháng), C5 là tổng số tiền mong muốn nhận được.
Hàm pmt() có 5 tham số rate, nper, pv, [fv], type:
1. Hàm pmt() tính ra số tiền bằng nhau phải gởi ngân hàng mỗi kỳ không phải tính tổng số tiền phải gởi.
2. Các tham số khác không được nói đến mà ngầm hiểu như hàm fv(), nên cũng sai tuốt luốt:
- nper là số kỳ gởi không phải thời gian gởi
- pv là present value = số tiền có sẵn trong ngân hàng tại thời điểm gởi tiền kỳ đầu tiên, thường là bằng không theo thí dụ cụ thể này. Mặc định của nó không bằng không.
- fv là giá trị tương lai (Future value), là số tiền mình mong mỏi sẽ nhận được sau khoảng thời gian tương đương nper kỳ gởi. Chính fv mới có mặc định bằng không nếu bỏ qua tham số.
- rate cũng phải quy về kỳ gởi.
GIẢI THÍCH TẠI SAO FV MẶC ĐỊNH BẰNG KHÔNG:
Pmt() được đặt ra để dùng tính số tiền bằng nhau phải trả mỗi kỳ trong nper kỳ, với lãi suất rate, cho 1 khoản vay hiện tại là pv; và đến cuối kỳ thứ nper, khoản vay được trả hết hoặc còn 1 khoản dư nợ fv.
Nói thêm:
Tham số nào có mặc định có thể bỏ qua thì trong cấu trúc hàm, nó được bao bằng dấu ngoặc vuông [].
Công thức: PMT(rate, nper, pv, fv, type)
Trong đó:
rate: lãi suất
nper: số kỳ
pv: giá trị hiện tại
fv: giá trị tương lai
type: thời điểm tính lãi (0: mặc định vào cuối kỳ, 1: đầu kỳ)
Ví dụ 1: Khoản vay $5,000 với lãi suất 7.5%/năm, kỳ hạn trong 2 năm. Tính khoản tiền trả gốc + lãi cố định vào đầu hàng tháng.
=Pmt(7.5%/12, 2*12, 5000, 0, 1)
Ví dụ 2: Khoản vay $8,000 với lãi suất 6%/năm, kỳ hạn trong 4 năm. Tính khoản tiền trả gốc + lãi cố định hàng tuần vào cuối kỳ.
=Pmt(6%/52, 4*52, 8000, 0, 0)
Ví dụ 3: Khoản vay $6,500 với lãi suất 5.25%/năm, kỳ hạn 10 năm. Tính khoản tiền trả gốc + lãi cố định vào cuối hàng năm.
=Pmt(5.25%/1, 10*1, 6500, 0, 0)
Ví dụ 4: Khoản vay $5,000 với lãi suất 8%/năm, kỳ hạn 3 năm, còn phải trả gốc sau 3 năm là $1,000. Tính khoản tiền trả gốc + lãi cố định vào cuối hàng tháng.
=Pmt(8%/12, 3*12, 5000, 1000, 0)
.
| ||||||||||
.
| 1 | Monthly | 0.00625 | 24 | $5,000 | $0 | 1 | -$223.60 | |
.
| 2 | Weekly | 0.001153846153846 | 208 | $6,000 | $0 | 0 | -$32.46 | |
.
| 3 | Yearly | 0.0525 | 10 | $6,500 | $0 | 0 | -$852.03 | |
.
| 4 | Monthly | 0.006666666666667 | 36 | $5,000 | $1,000 | 0 | -$321.83 | |
.
|
Hàm PPMT dùng để tính số tiền gốc mà bạn phải trả hàng tháng khi bạn vay tiền ở ngân hàng (đã biết trước lãi suất cho vay, số tiền vay và thời gian cho vay).
|
Ví dụ:
Bạn đang cần số tiền là 35000000 VND và tiến hành vay tại ngân hàng A
với lãi suất là 4,55% trong thời gian 120 tháng (10 năm). Ta sẽ dùng hàm
PPMT để tính toán số tiền mà bạn sẽ phải trả hàng tháng cho ngân hàng
như hình 3.
Cấu trúc hàm PPMT: PPMT(rate,per,nper,pv,[fv],[type])
Hàm ppmt() dùng để tính số tiền gốc phải trả tại kỳ thứ per trong tổng số nper kỳ, của 1 khoản tiền vay pv với lãi suất rate, mà sau kỳ thứ nper, số dư nợ của khoản vay còn là fv, mặc định fv =0
Như vậy rõ ràng ppmt() không phải để tính số tiền gốc mà bạn phải trả hàng tháng, mà chỉ tính số tiền gốc tại kỳ thứ per.
Tại sao phải tính riêng kỳ thứ per? Vì các khoản trả pmt là bằng nhau mỗi kỳ, nhưng với cách tính lãi trên dư nợ giảm dần thì lãi giảm dần, suy ra khoản trả gốc tăng dần: Khoản trả gốc không bằng nhau trong mỗi kỳ trả.
Cấu trúc hàm PPMT: PPMT(rate,per,nper,pv,[fv],[type])
Hàm ppmt() dùng để tính số tiền gốc phải trả tại kỳ thứ per trong tổng số nper kỳ, của 1 khoản tiền vay pv với lãi suất rate, mà sau kỳ thứ nper, số dư nợ của khoản vay còn là fv, mặc định fv =0
Như vậy rõ ràng ppmt() không phải để tính số tiền gốc mà bạn phải trả hàng tháng, mà chỉ tính số tiền gốc tại kỳ thứ per.
Tại sao phải tính riêng kỳ thứ per? Vì các khoản trả pmt là bằng nhau mỗi kỳ, nhưng với cách tính lãi trên dư nợ giảm dần thì lãi giảm dần, suy ra khoản trả gốc tăng dần: Khoản trả gốc không bằng nhau trong mỗi kỳ trả.
4. Hàm IPMT:
|
Hàm IPMT
dùng để tính số tiền lãi mà bạn phải trả hàng tháng. Ta sẽ sử dụng hàm
IPMT để tính cho trường hợp vay tiền ở ngân hàng A trên, tổng số tiền mà
bạn phải trả cho ngân hàng bao gồm số tiền gốc (tính bằng hàm PPMT) và
số tiền lãi (tính bằng hàm IPMT) (Hình 4).
Cụ thể, số tiền lãi hàng tháng được tính như sau: IPMT(B$2/12, A7, B$4, B$3)
Cụ thể, số tiền lãi hàng tháng được tính như sau: IPMT(B$2/12, A7, B$4, B$3)
|
Cụ thể trong ví dụ này, có thể thấy sau 120 tháng bạn đã trả đủ 35.000.000 VNĐ tiền vốn và 8.629.431,85 VNĐ tiền lãi cho ngân hàng
Tương tự PPMT(), IPMT() có cấu trúc: IPMT(rate,per,nper,pv,[fv],[type])
Hàm IPMT() dùng để tính số tiền lãi phải trả tại kỳ thứ per trong tổng số nper kỳ, của 1 khoản tiền vay pv với lãi suất rate, mà sau kỳ thứ nper, số dư nợ của khoản vay còn là fv, mặc định fv =0
Như vậy, IPMT() không phải để tính số tiền lãi mà bạn phải trả hàng tháng, mà chỉ tính số tiền lãi tại kỳ thứ per.
Lý do tương tự Ppmt()
5. Hàm NPV (Net Present Value) Giá trị hiện tại thuần trên Excel
Công thức: NPV(rate, value1, [value2],...)
Nếu dòng tiền (cash flows) phát sinh cuối mỗi kỳ (EOP), ta dùng công thức:
=Npv(rate, value1, value2, ... value_n ) - Initial Investment
Còn nếu dùng tiền phát sinh đầu mỗi kỳ (BOP), ta dùng công thức:
=Npv(rate, value2, ... value_n ) - Initial Investment + value1
NPV = PV of Future Cash Flow - Initial Investment
Ví dụ 1: một doanh nghiệp dự định đưa ra thị trường một dòng sản phẩm mới. Chi phí ban đầu để sản xuất sản phẩm này (tiền mua sắm máy móc, thiết bị, chi phí đào tạo nhân viên...) là $100,000. Các chi phí quản lý dự kiến là $5,000/năm. Doanh thu từ sản phẩm này dự kiến là $30,000/năm. Tỉ suất hoàn vốn là 10%. Sản phẩm này dự kiến sẽ bán trong vòng 6 năm.
Theo công thức ở trên ta có thể tính toán giá trị hiện tại thuần của dự án là:
=NPV(10%,(30000-5000),(30000-5000),(30000-5000),(30000-5000),(30000-5000),(30000-5000))-100000
Kết quả: $8,881.52>0
Công thức tính PMT() trong ô C10 của Thankyou như sau:
=PMT(C5/C6, C4*C6, -C7, -C8, C9)
6.P/s Ngoài:
1. Hai khoản tiền PV và FV luôn là 2 tham số trái dấu nhau. Tùy theo trường hợp thì PV dương, FV âm và ngược lại. Trường hợp trong file, 1 trong 2 tham sô bằng không, nên chưa thấy sai. Giải thích:
a. Trường hợp 1: Khi gửi tiền:
- PV
<> 0: PV là số tiền hiện có sẵn trong sổ tiền gửi tại thời điểm
gởi tiền, đồng nghĩa với 1 khoản chi ngay tại thời điểm đó (payable).
Theo quy ước thì khoản chi ra có giá trị âm.
- FV <> (đương nhiên), là khoản tiền sẽ nhận được trong tương lai (receivable). Theo quy ước thì khoản thu vào có giá trị dương.
- FV <> (đương nhiên), là khoản tiền sẽ nhận được trong tương lai (receivable). Theo quy ước thì khoản thu vào có giá trị dương.
b. Trường hợp 2: Khi vay tiền:
- PV <> 0 (đương nhiên), là khoản tiền nhận được khi vay tiền (receivable). Vậy PV > 0
- FV <> 0: là khoản tiền còn nợ lại sau 1 khoảng thời gian vay và trả dần. Khoản này cũng vẫn phải trả trong tương lai (payable), vậy FV < 0.
- FV <> 0: là khoản tiền còn nợ lại sau 1 khoảng thời gian vay và trả dần. Khoản này cũng vẫn phải trả trong tương lai (payable), vậy FV < 0.
c. Kết luận: PV và FV luôn trái dấu. Công thức trên của Thankyou với 2 dấu trừ sẽ sai trong trường hợp tổng quát.
2. Dấu của PMT: Dấu của PMT luôn luôn âm. Tuy nhiên thường ta muốn hiển thị số dương (theo ý thích), hãy đặt dấu trừ phía trước PMT() hoặc dùng hàm ABS(), không đặt dấu trừ bên trong hàm.
3. Trong ngân hàng chỉ có các kỳ thanh toán là tháng, quý, 6 tháng, 1 năm, (không có kỳ 2 tháng). Vậy số kỳ trong 1 năm (periods per year, không phải period) sẽ là 12, 4, 2, 1, (không có 6 kỳ 1 năm) File ví dụ
Mọi liên hệ qua email: dinhanhtuan68@gmail.com
Khóa đào tạo Power BI phân tích báo cáo để bán hàng thành công
KHÓA HỌC LẬP TRÌNH PYTHON TỪ CƠ BẢN ĐẾN CHUYÊN NGHIỆP
Khóa học Lập trình Visual Foxpro 9 - Dành cho nhà quản lý và kế toán
Khóa học hướng dẫn về Moodle chuyên nghiệp và hay
Xây dựng hệ thống đào tạo trực tuyến chuyên nghiệp tốt nhất hiện nay.
Khóa học AutoIt dành cho dân IT và Marketing chuyên nghiệp
Khoá học Word từ cơ bản tới nâng cao, học nhanh, hiểu sâu
Khóa học hướng dẫn sử dụng Powerpoint từ đơn giản đến phức tạp HIỆU QUẢ
Khóa học Thiết kế, quản lý dữ liệu dự án chuyên nghiệp cho doanh nghiệp bằng Bizagi
Khóa học Phân tích dữ liệu sử dụng Power Query trong Excel
Khóa học “Thiết kế bài giảng điện tử”, Video, hoạt hình
kiếm tiền Youtube bằng phần mềm Camtasia Studio
Khóa học HƯỚNG DẪN THIẾT KẾ VIDEO CLIP CHO DÂN MARKETING CHUYÊN NGHIỆP
HƯỚNG DẪN THIẾT KẾ QUẢNG CÁO VÀ ĐỒ HỌA CHUYÊN NGHIỆP VỚI CANVA
Hãy tham gia khóa học để trở thành người chuyên nghiệp. Tuyệt HAY!😲👍
GOOGLE SPREADSHEETS phê không tưởng
Khóa học sử dụng Adobe Presenter-Tạo bài giảng điện tử
Khóa học sử dụng Edmodo để dạy và học hiện đại để thành công
Cập nhật công nghệ từ Youtube tại link: congnghe.hocviendaotao.com
Tham gia nhóm Facebook
Để tham gia khóa học công nghệ truy cập link: http://thuvien.hocviendaotao.com
Mọi hỗ trợ về công nghệ email: dinhanhtuan68@gmail.com
