Trong hướng dẫn này, bạn sẽ tìm hiểu: nper có nghĩa là gì trong Excel? Làm thế nào để sử dụng hàm NPER để tính số kỳ cần thiết để trả một khoản vay hoặc số khoản đầu tư cần thiết để kiếm được số tiền mục tiêu? Và hơn thế nữa!
Khi xây dựng quỹ doanh nghiệp, các nhà phân tích tài chính thường muốn biết mất bao lâu để đạt được tập đoàn mong muốn. Khi đăng ký một khoản vay, bạn có thể muốn biết có bao nhiêu khoản thanh toán được yêu cầu để hoàn trả đầy đủ. Đối với những tác vụ như vậy, Excel cung cấp hàm NPER, viết tắt của “số khoảng thời gian”.
Hàm NPER trong Excel
NPER là một hàm tài chính Excel tính toán số kỳ thanh toán cho một khoản vay hoặc khoản đầu tư dựa trên các khoản thanh toán định kỳ bằng nhau và lãi suất không đổi.
Hàm có sẵn trong tất cả các phiên bản Excel 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 và Excel 2007.
Cú pháp như sau:
NPER (tỷ lệ, pmt, pv, [fv], [type])
Ở đâu:
- Tỷ lệ (bắt buộc) – lãi suất mỗi kỳ. Nếu bạn trả mỗi năm một lần, hãy ghi rõ lãi suất hàng năm; nếu bạn trả hàng tháng, hãy cung cấp lãi suất hàng tháng, v.v.
- Pmt (bắt buộc) – số tiền phải trả mỗi kỳ. Thông thường, nó bao gồm gốc và lãi, nhưng không có thuế hoặc phí.
- Pv (bắt buộc) – giá trị hiện tại của khoản vay hoặc khoản đầu tư, tức là hiện tại một loạt các dòng tiền trong tương lai có giá trị bao nhiêu.
- Fv (tùy chọn) – giá trị tương lai của khoản vay hoặc khoản đầu tư sau lần thanh toán cuối cùng. Nếu bỏ qua, mặc định là 0.
- Kiểu (tùy chọn) – chỉ định khi nào các khoản thanh toán đến hạn:
- 0 hoặc bị bỏ qua (mặc định) – vào cuối kỳ kế toán
- 1 – vào đầu kỳ kế toán
4 điều bạn nên biết về hàm NPER
Để sử dụng hiệu quả hàm NPER trong trang tính của bạn và tránh những lỗi thường gặp, vui lòng ghi nhớ những điều sau:
- Sử dụng tích cực số cho dòng vào (bất kỳ số tiền nào bạn nhận được) và tiêu cực số cho dòng ra (bất kỳ số tiền nào bạn phải trả).
- Nếu giá trị hiện tại (pv) bằng 0 hoặc bị bỏ qua, giá trị tương lai (fv) phải được bao gồm.
- Các tỷ lệ đối số có thể được nhập dưới dạng phần trăm hoặc số thập phân, ví dụ: 5% hoặc 0,05.
- Đảm bảo cung cấp tỷ lệ tương ứng với Chu kỳ. Ví dụ: nếu một khoản vay được trả hàng tháng với lãi suất hàng năm là 8%, thì hãy sử dụng 8% / 12 hoặc 0,08 / 12 cho tỷ lệ tranh luận.
Công thức NPER cơ bản trong Excel
Nắm rõ lý thuyết, hãy xây dựng công thức NPER ở dạng đơn giản nhất để có số kỳ thanh toán khoản vay dựa trên dữ liệu sau:
- Lãi suất hàng năm (tỷ lệ): C2
- Thanh toán hàng năm (pmt): C3
- Số tiền vay (pv): C4
Đặt các đối số lại với nhau, chúng ta nhận được công thức đơn giản sau:
=NPER(C2, C3, C4)
Tùy chọn [fv] và [type] các đối số bị bỏ qua vì giá trị tương lai không liên quan trong trường hợp này và các khoản thanh toán đến hạn vào cuối năm, đây là loại mặc định.
Giả sử khoản vay 10.000 đô la được đưa cho bạn với lãi suất 8% hàng năm và bạn sẽ trả 3.800 đô la cho ngân hàng mỗi năm, công thức cho thấy rằng bạn sẽ phải thực hiện 3 lần thanh toán hàng năm để trả lại số tiền đã vay.
Xin hãy chú ý đến nó pmt là một số âm bởi vì nó là tiền mặt.
Nếu bạn nhập một khoản thanh toán định kỳ dưới dạng số dương, sau đó đặt dấu trừ trước pmt đối số trực tiếp trong công thức:
=NPER(C2, -C3, C4)
Cách sử dụng hàm NPER trong Excel – ví dụ về công thức
Dưới đây, bạn sẽ tìm thấy thêm một vài ví dụ về công thức NPER trong Excel cho biết cách tính số kỳ thanh toán cho các tình huống khác nhau.
Tính số lần thanh toán định kỳ cho một khoản vay
Hầu hết các khoản vay thế chấp và các khoản vay dài hạn khác được trả góp hàng tháng. Một số khác được trả hàng quý hoặc nửa năm. Câu hỏi đặt ra là – làm thế nào để bạn tìm được số lần thanh toán định kỳ cần thiết để trả lại tổng số tiền vay?
Điều quan trọng là chuyển đổi lãi suất hàng năm thành tỷ lệ định kỳ. Đối với điều này, chỉ cần chia tỷ lệ hàng năm cho số kỳ mỗi năm:
Thanh toán hàng tháng: tỷ lệ = lãi suất hàng năm / 12
Thanh toán hàng quý: tỷ lệ = lãi suất hàng năm / 4
Thanh toán định kỳ hàng năm: tỷ lệ = lãi suất hàng năm / 2
Giả sử bạn đã vay 10.000 đô la với lãi suất 8% hàng năm, với khoản thanh toán hàng tháng là 500 đô la.
Chúng tôi bắt đầu với việc nhập dữ liệu vào các ô tương ứng:
- Lãi suất hàng năm (C2): 8%
- Thanh toán hàng tháng (C3): -500
- Số tiền cho vay (C4): 10.000
Để nhận được một giá trị chính xác cho tỷ lệ đối số, chia C2 cho 12. Và bạn sẽ nghĩ ra công thức sau để tính số thanh toán hàng tháng về khoản vay:
=NPER(C2/12, C3, C4)
Kết quả cho thấy sẽ mất 22 tháng (hoặc 1 năm 10 tháng) để trả hết khoản vay:
Nếu bạn thực hiện thanh toán hàng quý, sử dụng C2 / 4 cho tỷ lệ để buộc NPER trả lại các khoảng thời gian trong các quý:
=NPER(C2/4, C3, C4)
Ngoài ra, bạn có thể nhập số kỳ hạn mỗi năm vào một ô riêng biệt (C5) và chia lãi suất hàng năm (C2) cho ô đó:
=NPER(C2/C5, C3, C4)
Tính NPER dựa trên giá trị hiện tại và tương lai
Khi đầu tư hoặc tiết kiệm tiền, bạn thường biết mình muốn kiếm được bao nhiêu vào cuối kỳ. Trong trường hợp này, bạn có thể bao gồm giá trị tương lai mong muốn (fv) trong công thức.
Giả sử bạn muốn đầu tư 1.000 đô la với lãi suất hàng năm là 3% và đóng góp thêm 500 đô la vào cuối mỗi tháng. Mục tiêu của bạn là đạt 10.000 đô la.
Để tìm số lượng đầu tư hàng tháng, hãy nhập dữ liệu sau vào các ô xác định trước:
- Lãi suất hàng năm (C2): 3%
- Thanh toán hàng tháng (C3): -500
- Giá trị hiện tại (C4): -1,000
- Giá trị tương lai (C5): 10.000
- Khoảng thời gian mỗi năm (C6): 12
Nhập công thức này vào C8:
=NPER(C2/C6, C3, C4, C5)
Và bạn sẽ thấy rằng phải mất 18 khoản đầu tư hàng tháng để đạt được số tiền mục tiêu:
Xin hãy chú ý đến nó:
- Đối với tỷ lệ, chúng tôi chia lãi suất hàng năm cho số kỳ mỗi năm (trong trường hợp của chúng tôi là số tháng) để có được hàm NPER trả về các kỳ trong tháng.
- Cả hai pmt và pv là các số âm vì chúng đại diện cho một dòng chảy ra.
- Giả định rằng các khoản thanh toán đến hạn vào cuối kỳ, do đó kiểu đối số bị bỏ qua.
Một công thức chung để tìm NPER trong Excel
Xử lý các trường hợp cụ thể thật dễ dàng phải không? Làm thế nào về việc xây dựng một công thức chung thực hiện hoàn hảo công việc tính toán NPER trong Excel cho dù bạn đang đi vay hay tiết kiệm và bất kể bạn thanh toán bao lâu một lần? Đối với điều này, chúng tôi sẽ sử dụng hàm NPER ở dạng đầy đủ của nó và phân bổ các ô cho tất cả các đối số, bao gồm cả các đối số tùy chọn. Trong trường hợp của chúng tôi, đó sẽ là:
- Lãi suất hàng năm (tỷ lệ) – C2
- Thanh toán định kỳ (pmt) – C3
- Giá trị hiện tại (pv) – C4
- Giá trị tương lai (fv) – C5
- Khi các khoản thanh toán đến hạn (kiểu) – C 6
- Khoảng thời gian mỗi năm – C7
Giả sử bạn muốn đầu tư 10.000 đô la ban đầu với lãi suất hàng năm là 5% và sau đó thanh toán thêm 1.000 đô la vào đầu mỗi quý, nhằm đạt được 50.000 đô la. Bạn sẽ phải thực hiện bao nhiêu khoản thanh toán hàng quý?
Để có được định kỳ chính xác tỷ lệ, chúng tôi chia lãi suất hàng năm (C2) cho số kỳ mỗi năm (C7). Đối với các đối số khác, chỉ cần cung cấp các tham chiếu ô tương ứng:
=NPER(C2/C7, C3, C4, C5, C6)
Lời khuyên:
- Để bớt sai sót, bạn có thể tạo danh sách thả xuống chỉ cho phép các giá trị 0 và 1 cho kiểu tranh luận.
- Thông thường, khi bạn vay tiền, bạn thanh toán vào cuối kỳ để lãi suất tích lũy (kiểu được đặt thành 0). Khi cố gắng tiết kiệm, bạn quan tâm đến việc nhận được tiền càng sớm càng tốt để tích lũy tiền lãi, đó là lý do tại sao bạn phải trả vào đầu kỳ (kiểu được đặt thành 1).
Cách lấy hàm NPER trả về một số nguyên
Trong các ví dụ trên, số khoảng thời gian được tính toán được hiển thị dưới dạng số nguyên vì ô công thức được định dạng để không hiển thị vị trí thập phân. nếu bạn định dạng ô để hiển thị một hoặc nhiều chữ số thập phân, bạn sẽ thấy rằng kết quả của NPER thực sự là một số thập phân:
Tuy nhiên, trong cuộc sống thực, không thể có dấu chấm thập phân. Trong những tình huống như vậy, khoản thanh toán cuối cùng sẽ chỉ thấp hơn các kỳ trước.
Để nhận được số khoảng thời gian cần thiết chính xác, bạn có thể bao gồm công thức NPER của mình trong ROUNDUP hàm làm tròn giá trị lên đến một số chữ số được chỉ định. Vì chúng tôi muốn kết quả là một số nguyên, chúng tôi đặt num_digits đối số bằng 0:
ROUNDUP (NPER (), 0)
Với lãi suất hàng năm ở C2, số tiền thanh toán ở C3 và số tiền vay ở C4, công thức có dạng sau:
=ROUNDUP(NPER(C2, C3, C4), 0)
Như bạn thấy, số chu kỳ đầy đủ là 4 chứ không phải 3 như bạn nghĩ khi nhìn vào ảnh chụp màn hình ở trên. Bạn sẽ chỉ phải trả một số tiền rất nhỏ trong 4thứ tự năm.
Hàm NPER trong Excel không hoạt động
Nếu công thức NPER của bạn trả về lỗi hoặc kết quả sai, rất có thể đó sẽ là một trong những điều sau đây.
#NUM! lỗi
Xảy ra nếu giá trị được chỉ định trong tương lai (fv) không bao giờ có thể đạt được với một mức lãi suất nhất định (tỷ lệ) và thanh toán (pmt). Để có được kết quả hợp lệ, hãy cố gắng tăng lãi suất định kỳ và / hoặc số tiền thanh toán.
#GIÁ TRỊ! lỗi
Xảy ra nếu bất kỳ đối số nào không phải là số. Để sửa lỗi, hãy đảm bảo rằng không có số nào được sử dụng trong công thức được định dạng dưới dạng văn bản. Để biết thêm thông tin, vui lòng xem Cách chuyển đổi văn bản thành số.
Kết quả của hàm NPER là một số âm
Thông thường, sự cố này xảy ra khi các khoản thanh toán đi được biểu thị bằng số dương. Ví dụ: khi tính toán thời hạn thanh toán cho một khoản vay, hãy cung cấp số tiền vay (pv) dưới dạng một số dương và khoản thanh toán định kỳ (pmt) dưới dạng số âm. Khi đầu tư hoặc tiết kiệm tiền, khoản đầu tư ban đầu (pv) và thanh toán định kỳ (pmt) phải là giá trị âm và giá trị tương lai (fv) tích cực.
Đó là cách tìm NPER trong Excel. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của Ketoanmvb vào tuần tới!
The post Hàm NPER trong Excel với các ví dụ về công thức appeared first on Dịch Vụ kế toán tại Hà Nội | Uy tín - Chất lượng | Kế Toán MVB.
source https://ketoanmvb.com/ham-nper-trong-excel-voi-cac-vi-du-ve-cong-thuc.html
Không có nhận xét nào:
Đăng nhận xét